15

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 them from XLS to XLSX?

6

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

Note that you'll also need the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats for the OFC to work.
Both tools don't really seem to be supported anymore.

1
  • 1
    It appears quite a few people, including me, have issue with this tool failing to convert their xls files for no discernible reason. – John Jun 24 '16 at 9:51
4

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 above workbook.

Option Explicit

' Convert all xls files in selected folder to xlsx

Public 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
        On Error Resume Next ' Prevent debug mode if user cancels selection
        strConversionPath = .SelectedItems(1)
        On Error GoTo 0      ' Re-enable default error handling
    End With

    Set FSO = New Scripting.FileSystemObject

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

        ' Disable confirmation dialogs (to prevent "unsaved changes" dialog popping up)
        ' and screen updates (to speed up conversion)
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False

        ' Loop through files, find the .xls files
        For Each fFile In fFolder.Files
            If LCase$(Right(fFile.Name, 4)) = ".xls" Then
                ' Open temporary workbook
                Set wkbConvert = Workbooks.Open(fFile.Path)
                ' Save as OpenXML workbook - if your .xls files contain macros
                ' then change to FileFormat:=xlOpenXMLWorkbookMacroEnabled
                wkbConvert.SaveAs FSO.BuildPath(fFile.ParentFolder, _
                                    Left(fFile.Name, Len(fFile.Name) - 4)) & ".xlsx", _
                                  FileFormat:=xlOpenXMLWorkbook
                wkbConvert.Close SaveChanges:=False
                ' Delete original file
                fFile.Delete Force:=True
            End If
        Next fFile

        ' Re-enable confirmation dialogs and screen updates
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True

    End If

End Sub

Note: If the files you are converting contain macros then you would need to update the FileFormat:=xlOpenXMLWorkbook to read FileFormat:=xlOpenXMLWorkbookMacroEnabled. Or if you don't need the 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.

1
  • What if I want to convert .CSV to .XLSX? – Boris_yo Sep 22 '14 at 8:43
3

So I wrote a simple VBScript to convert .xls files to .xlsx in a silent fashion.

./convert-xls-xlsx.vbs {path to folder containing .xls files}

convert-xls-xlsx.vbs:

Set args = WScript.Arguments
strPath = args(0)
strPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(strPath)
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(strPath)
For Each objFile In objFolder.Files
    fileName = objFile.Path
    If (objFso.GetExtensionName(objFile.Path) = "xls") Then
        Set objWorkbook = objExcel.Workbooks.Open(fileName)
        saveFileName = Replace(fileName,".xls",".xlsx")
        objWorkbook.SaveAs saveFileName,51
        objWorkbook.Close()
        objExcel.Application.DisplayAlerts =  True
    End If
Next
MsgBox "Finished conversion"

NOTE: Look out for spaces in the folder path, if your path has a space in between, put the path in quotes.

2
  • I changed the DisplayAlerts to = False as I don't want to know about any macros, just delete them, that's why I'm converting these things in the first place. Then removed the final MsgBox, as I didn't want it bugging me. – BeowulfNode42 Apr 21 '20 at 3:10
  • @BeowulfNode42 cool, but I think I kept the DisplayAlerts just cause my workbooks were corrupt. But then again I don't remember exactly now. – brosahay Apr 22 '20 at 5:53
1

If you have LibreOffice or OpenOffice then you can use their CLI convert feature

for %f in (*.xls) do "path\to\libreoffice\soffice.com" --convert-to xlsx "%f"

%f needs to be changed to %%f in a batch file

In some versions you need to use libreoffice instead of soffice. And sometimes you need to add --headless before or after --convert-to to make it work

You can also use unoconv since it can convert "between any document format supported by LibreOffice/OpenOffice"

for %f in (*.xls) do unoconv -f xlsx "%f"
0

If you have MsOffice installed, then this tool might be worth a download for a quick fix.

http://www.softinterface.com/Convert-XLS/Features/Convert-XLS-To-XLSX.htm

When you select a folder to see the xls files converted, make sure you tick the convert tool option that uses MS Office to the conversion, not their own converter.

If you use their own converter you will lose colors in the cells and single sheet seems to come out. If you use the MsOffice as the converter seems to work nicely. Good tool for a quick fix.

0
Sub SaveAllAsXLSX()
Dim strFilename As String
Dim strDocName As String
Dim strPath As String
Dim wbk  As Workbook
Dim fDialog As FileDialog
Dim intPos As Integer
Dim strPassword As String
Dim strWritePassword As String
Dim varA As String
Dim varB As String
Dim colFiles As New Collection
Dim vFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
With fDialog
    .Title = "Select folder and click OK"
    .AllowMultiSelect = True
    .InitialView = msoFileDialogViewList
    If .Show <> -1 Then
        MsgBox "Cancelled By User", , "List Folder Contents"
        Exit Sub
    End If
    strPath = fDialog.SelectedItems.Item(1)
    If Right(strPath, 1) <> "\" Then strPath = strPath + "\"
End With
If Left(strPath, 1) = Chr(34) Then
    strPath = Mid(strPath, 2, Len(strPath) - 2)
End If
Set obj = CreateObject("Scripting.FileSystemObject")
RecursiveDir colFiles, strPath, "*.xls", True
For Each vFile In colFiles
        Debug.Print vFile
    strFilename = vFile
    varA = Right(strFilename, 3)
    If (varA = "xls" Or varA = "XLSX") Then
     Set wbk = Workbooks.Open(Filename:=strFilename)
       If wbk.HasVBProject Then
              wbk.SaveAs Filename:=strFilename & "m", FileFormat:=xlOpenXMLWorkbookMacroEnabled
            Else
               wbk.SaveAs Filename:=strFilename & "x", FileFormat:=xlOpenXMLWorkbook
            End If
            wbk.Close SaveChanges:=False
           obj.DeleteFile (strFilename)
    End If
Next vFile

End Sub
Public Function RecursiveDir(colFiles As Collection, _
                             strFolder As String, _
                             strFileSpec As String, _
                             bIncludeSubfolders As Boolean)

    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant

    'Add files in strFolder matching strFileSpec to colFiles
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colFiles.Add strFolder & strTemp
        strTemp = Dir
    Loop

    If bIncludeSubfolders Then
        'Fill colFolders with list of subdirectories of strFolder
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop

        'Call RecursiveDir for each subfolder in colFolders
        For Each vFolderName In colFolders
            Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
        Next vFolderName
    End If

End Function
Public Function TrailingSlash(strFolder As String) As String
    If Len(strFolder) > 0 Then
        If Right(strFolder, 1) = "\" Then
            TrailingSlash = strFolder
        Else
            TrailingSlash = strFolder & "\"
        End If
    End If
End Function
1
  • 10
    Instead of just pasting a bunch of code, could you maybe also explain a little what it does, how to use it, and what the limitations are (if any)? That'd be more helpful for future visitors. – slhck Nov 5 '13 at 11:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.