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

Chava2390

New Member
Joined
Aug 3, 2015
Messages
4
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!
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Assuming that the workbook which runs the code will be in the same directory as those containing the quotes:
Code:
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top