VBA Code to open multiple excel files in same folder and save them into ".XLSX" Format

[Chava2390]

Hello All,

I had an automated process that use to take all extracts from a system in ".XLS" format (A lot of quotes); however, the system I got them uploaded to is no longer accepting ".XLS" format, it only accepts ".XLSX" Format, the problem here is that IT does not want and will not invest in getting that option available in the quoting system as they say "just for one person, lots of money, very limited benefits".

What I need to do looks simple but gets too complicated to my limited VBA knowledge.

What is it what I need?
I need to be able to create a Macro that opens all quotes in .xls format (they are all in the same folder and they are over 125 quote files), then got them all saved in the ".XLSX" Format so that the other system I upload them to would take them as valid files.

Note: as they are all quotes, the file name varies with the customer and the name is never the same, is sort of related, similar, but it is not a standardized format.

I am able to do this only for one file, but when trying to get it done for several files, my creativity and knowledge don't really help at all.

Anyone that can really help me to create this code or give me direction, I would really appreciate it!

 

[JLGWhiz]

Assuming that the workbook which runs the code will be in the same directory as those containing the quotes:

Sub convertFileType()
Dim fName As String, fPath As String, wb As Workbook
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
    Do
        If Right(fName, 3) = "xls" Then
            Set wb = Workbooks.Open(fPath & fName)
            wb.SaveAs fPath & Left(fName, InStr(fName, ".") - 1) & ".xlsx", 51
            s = Timer + 0.5
            Do While Timer < s
                DoEvents
            Loop
            wb.Close False
        End If
        fName = Dir
    Loop While fName <> ""
End Sub

This was only checked that it would compile without error. There are two things you need to give attention to. 1) The file path is assumed to be the same for the target workbooks as for the host workbook. If this is not the case, then the fPath statement needs to be modified to reflect the actual directory path instead of the ThisWorkbook.Path statement. 2) The SaveAs execution as written will put the file with the updated file extension in the same directory as the .xls file, thus having two files with the same name in that directory, since the old file is not deleted as part of the SaveAs process. You might want to substitute the ultimate directory path for the fPath variable in the SaveAs statement.

 

[Chava2390]

Hello,
That worked like a charm, thank you very much, that's exactly what I wanted to create, I would just try to declare a specific cell as path for opening the files and another to saving them so that It can be dynamic and avoid modifying the path in the code everytime files are in different locations.

Once again, thank you very much!