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:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,293
Office Version
2013
Platform
Windows
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.
 

Chava2390

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

Forum statistics

Threads
1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top