Convert or save multiple worksheet of excel file as a pdf file – debugvba.com

save excel worksheet as pdf

Objective

To convert or save each worksheet of excel file as a pdf file.

Approach

In this VBA Excel automation we are opening a excel workbook with the help of a dialog box. Then the code loops through each worksheet in the opened workbook. Then each the selected range of a sheet is saved as a .pdf file. Output pdf files will be saved at same location where the source excel file was located.This VBA code is written in .xlsm (macro) file.

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.Loop through each worksheet of the opened workbook.
4.We have used range A1:B10 in the code. So, we shall save only this range in the pdf file.
5.Save the newly added workbook at the same path where source excel file was saved.

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 = ActiveWorkbook.Name
MyWorkbookPath = ActiveWorkbook.Path

'Looping through each worksheet in selected  file
For Each wSheet In ActiveWorkbook.Sheets
    
'Save each sheet as pdf file, the newly pdf file will be located where original excel file was located
 wSheet.Range("A1:B10").ExportAsFixedFormat Type:=xlTypePDF, filename:=MyWorkbookPath & "\" & wSheet.Name & ".pdf", Quality:=xlQualityStandard _
        , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    
Next wSheet

'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, it has 3 worksheets. Each worksheet will be converted to a seperate pdf file.

 

Save excel file as pdf vba


This sample excel file , which we had selected using a dialog box, had 3 worksheets as we can see in the image below.Each of these sheets will  be converted to pdf.

 

Save excel file as pdf vba

 

After the execution of the code, we can see the 3 output pdf files in the same folder where source excel file was located. These 3 pdf files have the same name as that of their source worksheet name. It means that 2018.pdf file will hold the data and name of worksheets(“2018”).

 

Save excel file as pdf vba

Notes

We can also save the entire workbook using similar approach.

Reference

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

Post you may like

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