Batch convert .xls to .xlsx with VBA without opening the workbooks

Question

I have a bunch of Excel-Workbooks in the old .xls format. I'd like to convert them to .xlsx using VBA. The following code accomplishes this task but it needs to open each workbook in order to save it again.

Dim wbk As Workbook
Set wbk = Workbooks.Open(filename:="C:\some\example\path\workbook.xls")
wbk.SaveAs filename:="C:\some\example\path\workbook.xlsx", _
FileFormat:=xlOpenXMLWorkbook, AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
wbk.Close SaveChanges:=False

Is there another way to do this task without the need to open each workbook? This is very time consuming with at least 30-100 workbooks.

Using [this method](http://stackoverflow.com/a/10380381/2521004) of looping through files in a folder and turning off `ScreenUpdating` should achieve a relatively quick solution unless your talking about multiple hundreds / thousands of spreadsheets. — Automate This, Mar 20 '15 at 13:49
@PortlandRunner Disabling `ScreenUpdating` is definetly a step in the right direction. But Excel still opens the workbooks under-the-hood which again means that the time needed is, if at all, reduced only a little. The loop does not fit my needs as I select the workbooks I want to convert in a special way. I added my workbook count to the question. — Tobias Kloss, Mar 20 '15 at 13:56
@PortlandRunner Im sorry but that does not work as `.xls` is a binary file while `.xlsx` are xml-based. Excel does really not like this, (aka can't open this file) — Tobias Kloss, Mar 20 '15 at 14:14
This might be useful --> http://superuser.com/questions/524119/batch-convert-xls-to-xlsx — Pankaj Jaju, Mar 20 '15 at 15:39

Arya · Answer 1 · 2015-03-20T16:58:07.077

Here is the piece of code to get what you are looking for:

Sub ChangeFileFormat()

    Dim strCurrentFileExt   As String
    Dim strNewFileExt       As String
    Dim objFSO              As Object
    Dim objFolder           As Object
    Dim objFile             As Object
    Dim xlFile              As Workbook
    Dim strNewName          As String
    Dim strFolderPath       As String

    strCurrentFileExt = ".xls"
    strNewFileExt = ".xlsx"

    strFolderPath = "C:\Users\Scorpio\Desktop\New folder"
    If Right(strFolderPath, 1) <> "\" Then
        strFolderPath = strFolderPath & "\"
    End If

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.getfolder(strFolderPath)
    For Each objFile In objFolder.Files
        strNewName = objFile.Name
        If Right(strNewName, Len(strCurrentFileExt)) = strCurrentFileExt Then
            Set xlFile = Workbooks.Open(objFile.Path, , True)
            strNewName = Replace(strNewName, strCurrentFileExt, strNewFileExt)
            Application.DisplayAlerts = False
            Select Case strNewFileExt
            Case ".xlsx"
                xlFile.SaveAs strFolderPath & strNewName, XlFileFormat.xlOpenXMLWorkbook
            Case ".xlsm"
                xlFile.SaveAs strFolderPath & strNewName, XlFileFormat.xlOpenXMLWorkbookMacroEnabled
            End Select
            xlFile.Close
            Application.DisplayAlerts = True
        End If
    Next objFile

ClearMemory:
    strCurrentFileExt = vbNullString
    strNewFileExt = vbNullString
    Set objFSO = Nothing
    Set objFolder = Nothing
    Set objFile = Nothing
    Set xlFile = Nothing
    strNewName = vbNullString
    strFolderPath = vbNullString
End Sub

this is the link for XL file format : https://msdn.microsoft.com/en-us/library/office/ff198017.aspx

'-----------------------------------------

A bit Modification: Check this code, i have only changed its extension name, but please check it with the compatibility... and let me know is it working for you...

Sub ChangeFileFormat_V1()

    Dim strCurrentFileExt   As String
    Dim strNewFileExt       As String
    Dim objFSO              As Object
    Dim objFolder           As Object
    Dim objFile             As File  'Object
    Dim xlFile              As Workbook
    Dim strNewName          As String
    Dim strFolderPath       As String

    strCurrentFileExt = ".xls"
    strNewFileExt = ".xlsx"

    strFolderPath = "C:\Users\Scorpio\Desktop\New folder"
    If Right(strFolderPath, 1) <> "\" Then
        strFolderPath = strFolderPath & "\"
    End If

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.getfolder(strFolderPath)
    For Each objFile In objFolder.Files
        strNewName = objFile.Name
        If Right(strNewName, Len(strCurrentFileExt)) = strCurrentFileExt Then
            strNewName = Replace(strNewName, strCurrentFileExt, strNewFileExt)
            Application.DisplayAlerts = False
            objFile.Name = strNewName
            Application.DisplayAlerts = True
        End If
    Next objFile

ClearMemory:
    strCurrentFileExt = vbNullString
    strNewFileExt = vbNullString
    Set objFSO = Nothing
    Set objFolder = Nothing
    Set objFile = Nothing
    Set xlFile = Nothing
    strNewName = vbNullString
    strFolderPath = vbNullString
End Sub

Thanks, but 'workbooks.open(...)' is the part I would like to avoid as this takes so long. Also I Do have working code, that is not what my question is about. — Tobias Kloss, Mar 20 '15 at 15:35
i think you have to open the file to saveAs it. 'Pankaj Jaju' has shared a link...chk that — Arya, Mar 20 '15 at 16:30
Ok. So what I am looking for apparently is not possible. The link does only confirm that. Could you add that to your answer so I can accept it without having to answer my own question? — Tobias Kloss, Mar 20 '15 at 16:38
i have added some codes... chk that, this is same as we rename the file using rename option — Arya, Mar 20 '15 at 16:58
@PankajJaju Yes, but my question was about VBA only. So that porting tool is sadly not in my range. — Tobias Kloss, Mar 23 '15 at 12:48
@Arya Thanks, but as I commented the other answer it is not possible to simply rename the files. — Tobias Kloss, Mar 23 '15 at 12:50
atleast try on some files... if you are converting .xls to .xlsx then there should be no prog.. try with some sample files — Arya, Mar 23 '15 at 14:58

Batch convert .xls to .xlsx with VBA without opening the workbooks

1 Answers1

Linked