VBA code to change file format of multiple files in a folder

Question

The following code below changes the file format of multiple files saved as .xml to .xlsx files. It simply opens the files in a specific folder and "Saves as" .xlsx. However I don't know how to make it run on all the files in my target folder. As of now it is only pointing to the 1st file in the folder.

Sub m_convertformat()
'
' m_convertformat Macro

Dim wb As Workbook
Dim sht As Worksheet
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
    .Title = "Select A Target Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    myPath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
myExtension = "*.xls"

'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
Do While myFile <> ""
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myPath & myFile)

        'Change the format
         ActiveWorkbook.SaveAs Filename:= _
        "S:\Xyz\abc.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

        End With

    'Save and Close Workbook
    wb.Close SaveChanges:=True

    'Get next file name
    myFile = Dir
Loop

'Message Box when tasks are completed
MsgBox "Task Complete!"

End Sub

See [Enumerate files in a directory using FileSystemObject](http://stackoverflow.com/documentation/vba/990/scripting-filesystemobject/9507/enumerate-files-in-a-directory-using-filesystemobject) or [Loop through files in a folder using VBA?](http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba) (and likely a bunch of others). — Comintern, Jul 25 '16 at 16:23
I believe it's how you set `myFile`. Try just `myFile = Dir(myPath)` and add an `If()` statement to see if it's `myExtension` type file. Right now, that becomes like, `myFile = Dir(C:\Users\Me\myFileFolder\.xls)`? The answer in @Comintern's second link should direct you. — BruceWayne, Jul 25 '16 at 16:29

score 1 · Accepted Answer · answered Jul 25 '16 at 16:31

There a few things in the code that need adjusting in order for it to work exactly as the text you wrote described. See the refactored code below.

'Target File Extension (must include wildcard "*")
myExtension = "*.xml" `- since you want to open xml files to save as xlsx

Then change

 'Change the format
         ActiveWorkbook.SaveAs Filename:= _
        "S:\Xyz\abc.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

To

   'Change the format
    wb.SaveAs Filename:= wb.Path & "\" Replace(wb.Name,".xml",".xlsx"), _
            FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Then delete this: End With

VBA code to change file format of multiple files in a folder

1 Answers1

Linked