To convert multiple xls files to xlsx using VBA Excel.
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.
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.
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.
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.
After selecting the input folder , we shall select the output folder.Initially it will be blank, since there will be no output xlsx file.
We had placed 2 xls files in the input folder for demonstration purpose as shown below.
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.
We can follow similar approach to convert xls to xlsm , pdf or csv format.
Post you may like