Save Excel as PDF with VBA

Since Excel 2010, it has been possible to save Excel files as PDF documents.  PDF was then and continues to be, a common file format for distributing documents.

The code examples below provide the VBA options to automate the creation of PDFs from Excel.  They can be used on their own or as part of larger processes.

Rather than going from Excel to PDF, you might want to go the other way; from PDF to Excel.  Check out this post for possible solutions for that: Get data from PDF into Excel.

Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

• My favorite tips and tricks direct to your inbox
• Exclusive content (which is only available to subscribers)
• FREE tools and downloads

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0019 Save Excel as PDF with VBA.zip.

Saving Excel workbooks, sheets, charts and ranges as PDF

This section contains the base code to save Excel as PDF from different objects (workbooks, worksheets, ranges and charts).  From a VBA perspective, it is the ExportAsFilxedFormat method combined with the Type property set to xlTypePDF that creates a PDF.

Save active sheet as PDF

The following code saves the selected sheets as a single PDF.

Sub SaveActiveSheetsAsPDF()

'Create and assign variables
Dim saveLocation As String
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

Save active workbook as PDF

To save all the sheets from a workbook use the following macro.

Sub SaveActiveWorkbookAsPDF()

'Create and assign variables
Dim saveLocation As String
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"

'Save active workbook as PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

Save selection as PDF

Sometimes, we only want to save a small part of a worksheet to a PDF.  The following code saves only the selected cells.

Sub SaveSelectionAsPDF()

'Create and assign variables
Dim saveLocation As String
saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"

'Save selection as PDF
Selection.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

Save a range as PDF

The macro below saves a specified range as a PDF.

Sub SaveRangeAsPDF()

'Create and assign variables
Dim saveLocation As String
Dim rng As Range

saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"
Set rng = Sheets("Sheet1").Range("A1:H20")

'Save a range as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation

End Sub

Save a chart as PDF

The VBA code below saves a specified chart as a PDF.

Sub SaveChartAsPDF()

'Create and assign variables
Dim saveLocation As String
Dim cht As Chart

saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"
Set cht = Sheets("Sheet3").ChartObjects("Chart 1").Chart

'Alternative using the active chart
'Set cht = ActiveChart

'Save a chart as PDF
cht.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

Notes for saving PDF documents

While the Filename property is optional, it is important if we want to know where the file has been saved and what it’s called.

• If the Filename property is not provided, the PDF saves in your default folder location using the Excel workbook’s name with the .pdf file extension.
• Where a file name is provided, but not a file path, the document saves in your default folder location with the name provided.
• When the .pdf file extension is not provided, the suffix is added automatically.
• If a PDF already exists in the save location specified, it will be overwritten.  It may be necessary to include file handling procedures to prevent overwriting existing documents and handle errors.

Selecting specific worksheets before saving as PDF

If more than one worksheet is active, the PDF created will include all the active sheets.  The following code selects multiple worksheets from an array before saving the PDF.

Sub SelectSheetsAndSaveAsPDF()

'Create and assign variables
Dim saveLocation As String
Dim sheetArray As Variant

saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"
sheetArray = Array("Sheet1", "Sheet2")

'Select specific sheets from workbook, the save all as PDF
Sheets(sheetArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

Looping and saving as separate PDFs

To save multiple PDFs quickly, we can use VBA to loop through sheets or charts and save each.

Loop through sheets

The following macro loops through each worksheet in the active workbook and saves each as its own PDF.

Sub LoopSheetsSaveAsPDF()

'Create variables
Dim ws As Worksheet

'Loop through all worksheets and save as individual PDF in same folder
'as the Excel file
For Each ws In ActiveWorkbook.Worksheets

    ws.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"

Next

End Sub

Loop through selected sheets

The following macro loops through the selected worksheets in the active workbook and saves each as its own PDF.

Sub LoopSelectedSheetsSaveAsPDF()

Dim ws As Worksheet
Dim sheetArray As Variant

'Capture the selected sheets
Set sheetArray = ActiveWindow.SelectedSheets

'Loop through each selected worksheet
For Each ws In sheetArray

    ws.Select

    ws.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"

Next ws

'Reselect the selected sheets
sheetArray.Select

End Sub

Loop through charts

The following code loops through each chart on the active sheet and saves each as a separate PDF.

Sub LoopChartsSaveAsPDF()

'Create and assign variables
Dim chtObj As ChartObject
Dim ws As Worksheet

Set ws = ActiveSheet

'Loop through all charts abd save as individual PDF in same folder
'as the Excel file
For Each chtObj In ws.ChartObjects

    chtObj.Chart.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "/" & chtObj.Name & ".pdf"

Next chtObj

End Sub

Other PDF export options

When using ExportAsFixedFormat there are other optional settings available:

'Open the document after it is saved - options are True / False
OpenAfterPublish:=False

'Include the Excel document properties into the PDF - options are True / False
IncludeDocProperties:=True

'Does the created PDF adhere to the Print Areas already set in the 
'worksheet - options are True / False
IgnorePrintAreas:=False

'Set the output quality of the created document - options are 
'xlQualityMinimum / xlQualityStandard
Quality:=xlQualityStandard

'The page to start printing from. If Excluded, will start from the first page
From:=1

'The page to print to. If Excluded, will go to the last page
To:=2

Example using all the options

The code below demonstrates how to use all the options within a single macro.  These options can be flexed to meet your requirements.

Sub SaveAsPDFOptions()

Dim saveLocation As String
saveLocation = "C:\Users\marks\Documents\myPDFFile.pdf"

'Example using all the options
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=saveLocation, _
    OpenAfterPublish:=False, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    Quality:=xlQualityStandard, _
    From:=1, To:=2

End Sub

Other fixed formats available

The Type property can also create XPS documents when it is set to xlTypeXPS, rather than xlTypePDF.  XPS is Microsoft’s own fixed file format, which is similar to PDF, but based on the XML language.  It is rarely used in the real world, but is an option if required.

Conclusion

Learning how to save Excel as PDF is a good time investment.  Each of these code snippets on there own are not that impressive.  However, when combined with the automated e-mail processes and document control, they are a real time saver.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts: