Batch convert XLS to XLSX

Question

I have to work with an old application that can only export XLS files and I write programs in .Net using the EPPlus library that is only capable of reading XLSX files.

What is the easiest way to batch convert from XLS to XLSX?

Answer 1

I'd recommend using a macro to process the files within a folder to convert them from xls to xlsx. This code assumes that the files are all located within one folder and that all xls files need to be converted, but if you wanted to select individual files this code could be updated.

This code would need to be run from an Excel 2007 or Excel 2010 workbook.

Option Explicit

' Convert all xls files in selected folder to xlsx

Sub convertXLStoXLSX()

    Dim FSO As Scripting.FileSystemObject
    Dim strConversionPath As String
    Dim fFile As File
    Dim fFolder As Folder
    Dim wkbConvert As Workbook


    ' Open dialog and select folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        strConversionPath = .SelectedItems(1)
    End With

    Set FSO = New Scripting.FileSystemObject

    ' Check if the folder exists
    If FSO.FolderExists(strConversionPath) Then
        Set fFolder = FSO.GetFolder(strConversionPath)

        ' Loop through files, find the .xls files
        For Each fFile In fFolder.Files
            If Right(fFile.Name, 4) = ".xls" Then
                Application.DisplayAlerts = False
                wkbConvert = Workbooks.Open(fFile.Path)
                ' Save as XML workbook - if file contains macros change FileFormat:=52
                wkbConvert.SaveAs FSO.BuildPath(fFile.ParentFolder, Left(fFile.Name, Len(fFile.Name) - 4)) & ".xlsx", FileFormat:=51
                wkbConvert.Close SaveChanges:=False
                ' Delete original file
                fFile.Delete Force:=True
                Application.DisplayAlerts = True
            End If
        Next fFile

    End If

End Sub

If the files you are converting contain macros then you would need to update the 'FileFormat:=51' to read 'FileFormat:=52'. Or if you don't need to macro code in the converted files you could leave it alone and it will remove the macros when it converts it to the xlsx format.

Answer 2

Check out Office Migration Planning Manager.

The toolkit also contains the Office File Converter (OFC), which enables bulk document conversions from binary to OpenXML formats. (Technet)

Overview on Technet

Download Link