VBA – Convert XLS to XLSX

I was trying to help someone who asked a question in another one of my posts and ended up creating the following procedure that can convert an older xls Excel file into the newer xlsx format and thought it might be helpful to others.

'---------------------------------------------------------------------------------------
' Procedure : XLS_ConvertXLS2XLSX
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Converts an xls (2003-) into an xlsx (2007+)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sXLSFile  : String - XLS file path, name and extension to be converted
' bDelXLS   : True/False - Should the original XLS file be deleted after the conversion
'
' Usage:
' ~~~~~~
' Call XLS_ConvertXLS2XLSX("C:TempTest.xls")
' Call XLS_ConvertXLS2XLSX("C:TempTest.xls", False)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2018-02-27              Initial Release
'---------------------------------------------------------------------------------------
Function XLS_ConvertXLS2XLSX(ByVal sXLSFile As String, Optional bDelXLS As Boolean = True)
          '#Const EarlyBind = True 'Use Early Binding, Req. Reference Library
    #Const EarlyBind = False    'Use Late Binding
    #If EarlyBind = True Then
              'Early Binding Declarations
              Dim oExcel            As Excel.Application
              Dim oExcelWrkBk       As Excel.WorkBook
    #Else
              'Late Binding Declaration/Constants
              Dim oExcel            As Object
              Dim oExcelWrkBk       As Object
              Const xlOpenXMLWorkbook = 51
    #End If
          Dim bExcelOpened          As Boolean
 
          'Start Excel
10        On Error Resume Next
20        Set oExcel = GetObject(, "Excel.Application")      'Bind to existing instance of Excel
30        If Err.Number <> 0 Then      'Could not get instance of Excel, so create a new one
40            Err.Clear
50            On Error GoTo Error_Handler
60            Set oExcel = CreateObject("Excel.Application")
70            bExcelOpened = False
80        Else      'Excel was already running
90            bExcelOpened = True
100       End If
110       On Error GoTo Error_Handler
 
120       oExcel.ScreenUpdating = False
130       oExcel.Visible = False     'Keep Excel hidden until we are done with our manipulation
140       Set oExcelWrkBk = oExcel.Workbooks.Open(sXLSFile)
150       oExcelWrkBk.SaveAS Replace(sXLSFile, ".xls", ".xlsx"), xlOpenXMLWorkbook, , , , False
160       oExcelWrkBk.Close False
170       If bExcelOpened = True Then oExcel.Quit
 
180       If bDelXLS = True Then Kill (sXLSFile)
 
Error_Handler_Exit:
190       On Error Resume Next
200       Set oExcelWrkBk = Nothing
210       Set oExcel = Nothing
220       Exit Function
 
Error_Handler:
230       MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: XLS_ConvertXLS2XLSX" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occurred!"
240       oExcel.ScreenUpdating = True
250       oExcel.Visible = True     'Make excel visible to the user
260       Resume Error_Handler_Exit
End Function

12 responses on “VBA – Convert XLS to XLSX”

  2. Craig M

    This was just what I was looking for! I did encounter an issue though, to note, I am using Excel 365 and 2016.
    I received an error with Option Explicit declared at top… the error was on line 150 as shown above in the string reference strFilePath… there is no reference to this variable in the function. I changed it to reflect the sXLSFile string variable and it worked.
    Also, when I ran the code (my requirement was to convert two files), after the first file completed, I received another error code and the code tried to close all existences of excel, including the workbook I was using to convert the files. I commented out line 170 If bExcelOpened = True Then oExcel.Quit and then was able to make the second call to the function. Everything was great after this…
    Thought I would post this, in case others were having any trouble.

    Thanks for the code… again, was exactly what I was looking for!

    1. Daniel Pineault Post author

      I’d need more information to better help you, but I did give a couple usage examples in the function header; simply call the function and supply the path/filename of the Excel Workbook to convert. That’s basically it.

  4. BatchMan

    You have an error in your code…
    oExcelWrkBk.SaveAS Replace(strFilePath, “.xls”, “.xlsx”), xlOpenXMLWorkbook, , , , False

    Must be replaced by:
    oExcelWrkBk.SaveAS Replace(sXLSFile , “.xls”, “.xlsx”), xlOpenXMLWorkbook, , , , False

  5. Michael

    Hi there,

    I’m trying to call your function to convert a group of xls file but i’m not that good yet in this language, could you help me ?

    Was thinking by creating a loop to change the file path everytime that the function end.

    1. Daniel Pineault Post author

      You could do something like (untested aircode):

          Dim sFile                 As String
    Dim sPath                 As String

    sPath = "C:\Temp\"    'Folder to iterate over the xls files and convert to xlsx
    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    sFile = Dir(sPath & "*.xls")
    Do While sFile <> vbNullString
        If sFile <> "." And sFile <> ".." Then
            '            sFile 'FileName, does not include the path
            Call XLS_ConvertXLS2XLSX(sPath & sFile, False) 'False, to perform the conversion but retain the original xls files
        End If
        sFile = Dir     'Loop through the next file that was found
    Loop
      1. Michael

        Thanks for your answer,

        424 Error – Object Required
        Only the first file is converted.

        Seems like this line cause the problem :
        140 Set oExcelWrkBk = oExcel.Workbooks.Open(sXLSFile)

        1. Daniel Pineault Post author

          I’m not sure what to tell you, I just tested the code and it worked fine and converted over 20 workbooks in a row. There must be something else in your code causing an issue.

  6. Alain CHAZOT

    Hi,

    very nice job ! It works very well ! Thanks a lot.

    Do you have the same for Word and Powerpoint files (doc –> docx and ppt –> pptx) ?

    If yes, could you send me this code by email, please ?

    Alain