Save Excel file (workbook) as pdf file in VBA Excel –

Save excel file as pdf vba


To save an excel file as pdf using VBA excel.


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.


  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.



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

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.


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


Post you may like

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