Save or convert multiple excel files as pdf using VBA Excel – Debugvba.com

Save multiple excel files as pdf

Objective

To save multiple excel files as pdf using VBA Excel.

Approach

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.

 

Steps

  1. Select the input folder by the dialog box, this folder will contain the .xlsx files
  2. Select the output folder by dialog box, output pdf files will be created in this folder
  3. We are not making any changes in the source xlsx files here
  4. Output files will be placed in output folder, which we had selected earlier

 

Code

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.

 

Save multiple excel files as pdf

 


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.

 

Save multiple excel files as pdf

 

 

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.

 

Save multiple excel files as pdf

 

 

As we can see below, that before execution of the code we had two excel files in the input folder.

 

Save multiple excel files as pdf


Once the VBA code is executed,2 output pdf files are created automatically, and they are saved in the output folder.

 

Save multiple excel files as pdf

Notes

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.

 

Reference

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

Post you may like

Copy paste charts from excel to Powerpoint slide in VBA | debugvba.com