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?
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)
Note that you'll also need the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint File Formats
for the OFC to work.
Neither tool seems to be supported anymore.
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 readFileFormat:=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.
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.
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
You can also do that in PowerShell
ls -R *.xls |% { & "path\to\libreoffice\soffice.com" --convert-to xlsx $_.FullName }
# Or the full unaliased version:
Get-ChildItem -Recurse *.xls | ForEach-Object { `
& "path\to\libreoffice\soffice.com" --convert-to xlsx $_.FullName }
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"
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.
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