Convert multiple files from XLS to XLSX

[shreeram.maroo]

Hi Hans,
Need one more help.
I have got a bunch of .xls files in a folders.
I want to convert each of them in .xlsx separately.
Is there any shortcut ?

[StuartR]

This was posted as a reply to the topic Count Cells in excel

Since it is about a completely new subject, I have moved it to its own thread, and change the title.

[HansV]

The following macro will convert all .xls files in a folder and all its subfolders to .xlsx or .xlsm, depending on whether the workbook contains macros.

Code: Select all

Sub ConvertToXlsx()
    Dim fso As Object
    Dim fld As Object
    Dim strPath As String

    Application.ScreenUpdating = False
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' Specify the folder
    strPath = "C:\Test"
    Set fld = fso.GetFolder(strPath)
    Call ProcessFolder(fld)
    Application.ScreenUpdating = True
End Sub
 
Sub ProcessFolder(fld As Object)
    Dim sfl As Object
    Dim fil As Object
    Dim wbk As Workbook
 
    ' Loop through the files
    For Each fil In fld.Files
        If Right(fil.Name, 3) = "xls" Then
            Set wbk = Workbooks.Open(fil)
            If wbk.HasVBProject Then
                wbk.SaveAs fil & "m", 52
            Else
                wbk.SaveAs fil & "x", 51
            End If
            wbk.Close False
        End If
    Next fil
 
    ' Loop through the subfolders
    For Each sfl In fld.SubFolders
        ' Call ProcessFolder recursively
        Call ProcessFolder(sfl)
    Next sfl
End Sub

Change the line

Code: Select all

    strPath = "C:\Test"

to the path of the folder you want to use.

Warning: if you have many workbooks, the macro will take some time to run.

[shreeram.maroo]

Thanks a lot Hans.