Save Excel file (workbook) as pdf file in VBA Excel – debugvba.com

Save excel file as pdf vba

Objective

To save an excel file as pdf using VBA excel.

Approach

In this VBA excel automation we are opening a excel workbook by using a dialog box. Then selected workbook is saved as a pdf file by this VBA code. Name of the output pdf file will be same as source excel workbook.

Steps

  1. Open the source excel file using the dialog box.
  2. Store the name of the opened workbook and its location in 2 variables, they will be used for navigation and saving file.
  3. Save the workbook as a pdf file.
  4. Output pdf file will be saved at same location where source excel file is located.

 

Code

Sub SplitAndSaveAsPdf()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim SourceExcelFile As Variant
Dim MyWorkBook As String
Dim MyWorkbookPath As String
Dim wSheet As Worksheet

'Open only excel files using dialog box
SourceExcelFile = Application.GetOpenFilename(FileFilter:="Excel Files,*.xlsx")
Workbooks.Open (SourceExcelFile)

'Storing the name of selected workbook for future use
MyWorkBook = Replace(ActiveWorkbook.Name, ".xlsx", "")
MyWorkbookPath = ActiveWorkbook.Path

'Looping through each worksheet in selected  file

'Save each sheet as pdf file, the newly pdf file will be located where original excel file was located
 Workbooks(MyWorkBook).ExportAsFixedFormat Type:=xlTypePDF, filename:=MyWorkbookPath & "\" & MyWorkBook & ".pdf", Quality:=xlQualityStandard _
        , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    

'Close source excel  file
Windows(MyWorkBook).Close

End Sub

 

We have created a command button on the macro file(.xlsm), and this command button is assigned the VBA code.

 

Save excel file as pdf vba

 

Once we click on the command button, a dialog box will be shown. We shall select a source excel workbook using this dialog box.

Save excel file as pdf vba

 

 

This is the source excel file, which we shall use.

 

Save excel file as pdf vba

After the execution of the code, we can see the 1 output pdf files in created in the same folder where source excel file was placed. This output pdf file has the same name as that of their source workbook’s name. It means that 3_Years_Data.pdf file will hold the data and name of 3_Years_Data.xlsx.

 

Save excel file as pdf vba

 

Here data in each worksheet in the source workbook, will be pasted to a separate page in pdf file.

Note

By following this approach, we can dynamically create a separate pdf file for multiple workbooks as well.

Reference

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

Post you may like

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