Converting from IQy to XLSX with VBA

Question

I have a about 40 files that are IQy files that I can open with Excel and I'm trying to go through all of them and save them as xlsx files. What I have so far in VBA is this

Sub ConvertFiles()
    Dim Filename, Pathname As String
    Dim wb As Workbook

    Pathname = "C:\Users\CHI\Downloads"
    Filename = Dir(Pathname & ".iqy")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        wb.SaveAs Pathname & Filename & ".xlsx"
        wb.Close
        Filename = Dir()
    Loop
End Sub

To my understanding this loops through my download file where the iqy files are stored and then saveas in xlsx format. When I run it nothing happens.

UPDATE

Sub ConvertFiles()
    Dim Filename, Pathname As String
    Dim wb As Workbook

    Pathname = "C:\Users\CHI\Downloads\"
    Filename = Dir(Pathname & "*.iqy")
    Application.DisplayAlerts = False
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        wb.SaveAs Pathname & Filename & ".xlsx", FileFormat:=51
        wb.Close
        Filename = Dir()
    Loop
End Sub

This is what worked for me, the only problem I have now is after it changes every file I get a prompt to import data and all I have to press is ok. Is there a way to automate this part so that I can import the data using the table option.

Answer 1

You need to include a wildcard in order to find your iqy files and your pathname will need an additional folder separator to allow the Open and SaveAs to work:

Sub ConvertFiles()
    Dim Filename, Pathname As String
    Dim wb As Workbook

    Pathname = "C:\Users\CHI86786\Downloads\"
    Filename = Dir(Pathname & "*.iqy")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        wb.SaveAs Pathname & Filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        wb.Close
        Filename = Dir()
    Loop
End Sub

Lastly, to be sure it saves correctly, set the FileFormat parameter when using SaveAs.