VBA code to change file format of multiple files i

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

Answer 1

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