To save multiple excel files as pdf using VBA Excel.
In this VBA Excel automation, we are converting multiple excel(.xlsx) files to pdf files. Herein, we have placed multiple xlsx files in input folder, then we execute the VBA macro code from a separate macro file(.xlsm). Once the VBA code is executed successfully, we get the converted pdf files in the output folders.
- Select the input folder by the dialog box, this folder will contain the .xlsx files
- Select the output folder by dialog box, output pdf files will be created in this folder
- We are not making any changes in the source xlsx files here
- Output files will be placed in output folder, which we had selected earlier
Sub ExportExcelFilesToPDF() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim OpenSourceFolder As Object Dim OpenTargetFolder As Object Dim SelectedExcelFilesFolder As String Dim SelectedPdfFilesFolder As String Dim InputExcelFile As String Dim MyOpenedExcel As Workbook Dim OutputPdfFile As String Set OpenSourceFolder = Application.FileDialog(msoFileDialogFolderPicker) Set OpenTargetFolder = Application.FileDialog(msoFileDialogFolderPicker) 'Select input data folder MsgBox ("Select a --SOURCE data folder -- where excel files are located") Set OpenSourceFolder = Application.FileDialog(msoFileDialogFolderPicker) If OpenSourceFolder.Show = -1 Then SelectedExcelFilesFolder = OpenSourceFolder.SelectedItems(1) End If AppActivate Application.Caption 'Select output folder MsgBox ("Select a --TARGET folder -- where output PDF files will be saved") If OpenTargetFolder.Show = -1 Then SelectedPdfFilesFolder = OpenTargetFolder.SelectedItems(1) End If 'Looping through only xlsx files in input file folder InputExcelFile = Dir(SelectedExcelFilesFolder & "\*.xlsx") While InputExcelFile <> "" Set MyOpenedExcel = Workbooks.Open(SelectedExcelFilesFolder & "\" & InputExcelFile) OutputPdfFile = SelectedPdfFilesFolder & "\" & Replace(ActiveWorkbook.Name, "xlsx", "pdf") 'Save each excel file as pdf file, the newly pdf file will be located where original excel file was located ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=OutputPdfFile, Quality:=xlQualityStandard _ , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False MyOpenedExcel.Close InputExcelFile = Dir Wend End Sub
In the image below, you can see that we had created a command button on the macro(.xlsm) file. Once we click on it, VBA code will execute.
Once we click on the command button, a dialog box will be shown. It will ask us to choose the input folder, which has the .xlsx files.Image is shown below for the same.
After selecting the input folder, another dialog box will appear, it will ask us to select the output folder. Converted output files will be saved in it.
As we can see below, that before execution of the code we had two excel files in the input folder.
Once the VBA code is executed,2 output pdf files are created automatically, and they are saved in the output folder.
1.We can also use the folder path, by creating 2 variables for path as well.
2.We can also apply similar approach to convert word doc (.doc) to pdf document as well.
Post you may like