Convert multiple xls files to xlsx using VBA Excel

batch convert xls to xlsx vba excel

Objective

To convert multiple xls files to xlsx using VBA Excel.

Approach

Here we shall first create 2 folders, one for the input “xls” file and second one for the output “xlsx” file. Here we shall place all the input excel files in the input folder while all the converted output files will be placed in output folder.

Here we had written the code in a macro file (.xlsm), we had placed a command button on that macro file, we shall see that in the later part of this post. Once we click on this command button, a message box will appear, which will ask to select the input folder. Then a dialog box will appear, wherein we shall select the input folder.

Once we select the input folder, another message box will appear, which will ask to select the output folder. We shall select the output folder by using dialog box.Here we are looping through each xls file in the input folder, we have opened each xls file in the input folder,then “saved as” this file in output folder as xlsx format, then closed the opened xls file. Then we move to next xls file.This process keeps on repeating till last file is saved as xlsx formats.

Once the code is executed further,  all the output files will be saved in the output folder, we shall see that in the images below in later part of this post. We can give any name to input and output folder ,as code is independent of the folder names.

Code

Sub ConvertMultipleXlsToXlsx()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim OpenSourceFolder As Object
Dim OpenTargetFolder As Object

Dim SelectedExcelFilesFolder As String
Dim SelectedXlsxFilesFolder As String

Dim InputXlsFile As String
Dim MyOpenedXlsFile As Workbook
Dim ConvertedXlsxlFile As String


Set OpenSourceFolder = Application.FileDialog(msoFileDialogFolderPicker)
Set OpenTargetFolder = Application.FileDialog(msoFileDialogFolderPicker)

'Select input data folder
MsgBox ("Select a  folder where  excel files are located")
Set OpenSourceFolder = Application.FileDialog(msoFileDialogFolderPicker)

If OpenSourceFolder.Show = -1 Then
SelectedExcelFilesFolder = OpenSourceFolder.SelectedItems(1)
End If

'Handle case when no input folder is selected
If SelectedExcelFilesFolder = "" Then
MsgBox "No input folder selected, code will exit"
Exit Sub
End If

AppActivate Application.Caption

'Select output folder
MsgBox ("Select a folder where output XLSX files will be saved")
If OpenTargetFolder.Show = -1 Then
SelectedXlsxFilesFolder = OpenTargetFolder.SelectedItems(1)
End If

'Handle case when no output folder is selected
If SelectedXlsxFilesFolder = "" Then
MsgBox "No output folder selected, code will exit"
Exit Sub
End If

'Looping through only xls files in input file folder
InputXlsFile = Dir(SelectedExcelFilesFolder & "\*.xls")
While InputXlsFile <> ""
          
Set MyOpenedXlsFile = Workbooks.Open(SelectedExcelFilesFolder & "\" & InputXlsFile)
ConvertedXlsxlFile = SelectedXlsxFilesFolder & "\" & Replace(ActiveWorkbook.Name, "xls", "xlsx")
    
'Save each excel file as pdf file, the newly pdf file will be located where original excel file was located
ActiveWorkbook.SaveAs Filename:=ConvertedXlsxlFile, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
   
MyOpenedXlsFile.Close
InputXlsFile = Dir
Wend

End Sub

 

To begin with we had created 2 folders, one is “input” folder, while another one is “output” folder.Apart from these 2 folders we have placed the excel macro file in the shown folder, which we shall use to convert the file types.

 

batch convert xls to xlsx vba excel

First we had created a command button on the macro file.We shall click on this button to execute above mentioned VBA code, we can see the command button in the image below.

 

convert xls to xlsx vba excel

 

When we shall click on this command button, first a message box will be shown , then a dialog box will show up, we shall select the input folder by using that. We had placed the xls files in this folder.We shall convert these xls files to xlsx format.

 

batch convert xls to xlsx vba excel

 

After selecting the input folder , we shall select the output folder.Initially it will be blank, since there will be no output xlsx file.

 

batch convert xls to xlsx vba excel

 

We had placed 2 xls files in the input folder for demonstration purpose as shown below.

 

batch convert xls to xlsx vba excel

Once we execute the VBA code, both the “xls” files are converted into “xlsx”.We can convert as many files in one batch using this approach. We can see that in the image below that 2 xls files are converted to xlsx files.So, here we have achieved our objective of converting multiple xls files to xlsx files.We can see in the image below that converted files are saved in “output” folder.

 

batch convert xls to xlsx vba excel

Note

We can follow similar approach to convert xls to xlsm , pdf or csv format.

Post you may like

Split Text Data in Column by VBA Excel

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
https://docs.microsoft.com/en-us/office/vba/api/excel.application.filedialog