convert xls to xlxs files.

[Sharksfan]

I found a macro online that works to convert .xls files to .xlsx files. But it works for one file at a time. I would like to modify this code so it loops through all the files found in a folder. Folder path is static (does not change). Currently I have these files in C:\Users\cp023109\Desktop\Temp. Here is the code I found online. I am a newb so explanations of new code would be helpful for me to learn.

Sub Convert_XL_File_Versions()
Dim sFileName As String ' This will hold the original file we want to convert.
Dim sNewFileName As String ' We're going to use this for storage after assembling the new file name.
Dim wkbk As Workbook ' We'll use a workbook object to make referring to the source book easier.
Dim lCalc As Long ' We're going to use this to help speed up the processing.

Const sEXT As String = ".xlsx" ' We'll use this for the Excel 2007 fil extension

sFileName = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "SELECT THE EXTRACTED DATABASE FILE")

If sFileName = "False" Then
MsgBox "Cancel was clicked. Operation aborted.", vbOKOnly + vbExclamation
Exit Sub
End If

Application.Calculation = xlCalculationManual

sNewFileName = Left(sFileName, Len(sFileName) - 4) & sEXT

wkbk.SaveAs Filename:=sNewFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Set wkbk = Nothing
Set wkbk = Workbooks.Open(Filename:=sNewFileName)
wkbk.Close True
Kill sFileName

Application.Calculation = lCalc
Application.ScreenUpdating = True

MsgBox "The Excel file conversion is complete!", vbOKOnly + vbInformation

End Sub

 

[DanteAmor]

Use this. Review your folder path in red letters

Sub Convert_XL_File_Versions()
    Dim wPath As String, wFile As Variant, wNew As String, l2 As Workbook
    
    wPath = "[COLOR=#ff0000]C:\Users\cp023109\Desktop\Temp\[/COLOR]"
    If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
    wFile = Dir(wPath & "*.xls")
    Do While wFile <> ""
        If Right(wFile, 3) = "xls" Then
            Set l2 = Workbooks.Open(wPath & wFile)
            wNew = Left(wFile, Len(wFile) - 4) & ".xlsx"
            l2.SaveAs Filename:=wNew, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            l2.Close
            Kill wPath & wFile
        End If
        wFile = Dir()
    Loop
    MsgBox "The Excel file conversion is complete!", vbOKOnly + vbInformation
End Sub

 

[Sharksfan]

This works well. Thank you. I was in an panic as this saves the converted files in the documents folder. I didn't know this and had to search for the converted files. Is there a way for this macro to save the converted files back in the TEMP folder and only delete the xls files?

 

[DanteAmor]

This works well. Thank you. I was in an panic as this saves the converted files in the documents folder. I didn't know this and had to search for the converted files. Is there a way for this macro to save the converted files back in the TEMP folder and only delete the xls files?

Try this

Sub Convert_XL_File_Versions()    Dim wPath As String, wFile As Variant, wNew As String, l2 As Workbook
    
    wPath = "C:\Users\cp023109\Desktop\Temp\"
    If Right(wPath, 1) <> "\" Then wPath = wPath & "\"
    wFile = Dir(wPath & "*.xls")
    Do While wFile <> ""
        If Right(wFile, 3) = "xls" Then
            Set l2 = Workbooks.Open(wPath & wFile)
            wNew = Left(wFile, Len(wFile) - 4) & ".xlsx"
            l2.SaveAs Filename:=wPath & wNew, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
            l2.Close
            Kill wPath & wFile
        End If
        wFile = Dir()
    Loop
    MsgBox "The Excel file conversion is complete!", vbOKOnly + vbInformation
End Sub

If you do not want to delete the files, delete this line:

 Kill wPath & wFile