This post may contain affiliate links. Please read my disclosure for more info. Power Query Course

VBA code to save Excel files as PDF

VBA Code Snippets

Since Excel 2010 it has been possible to save Excel files as PDF documents.  The code below provides the VBA options to do achieve this.  These VBA Code Snippets can be used in much larger processes to automate the distribution of hundreds of document in just a few seconds.

Saving Excel workbooks, sheets, charts and ranges as PDF documents

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

'Save active workbook as PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

'Save selection as PDF
Selection.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

'Save a chart as PDF
ActiveChart.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

'Save a range as PDF
Sheets("Sheet1").Range("A1:H20").ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

Basic notes for saving PDF documents

  • If the Filename property is not provided, the PDF document is saved with the PDF file extension in the same folder and with the same name as the Excel document.
  • If the Filename property excludes the file path, but includes a file name, the document is saved into the same folder as the Excel document, but with the specified filename.
  • If the Filename property does not include .pdf at the end, it will use the last section of text after the final “\” as the file name and add the .pdf suffix automatically.
  • If a PDF already exists in the location with that file name, it will be overwritten.  It may be necessary 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 created PDF will include all the active sheets.  The following code selects multiple worksheets before saving the PDF.

'Select specific sheets from workbook, the save all as PDF
Sheets(Array("Sheet1", "Sheet2")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Documents\Saved PDF.pdf"

Loop through each worksheet and save as separate documents

It is possible to loop through each worksheet in the active workbook and save each into its own PDF.

'Loop through all worksheets and save as individual PDF in same folder 
'as the Excel file
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ws.Name
Next

Other PDF Export Options

When using ExportAsFixedFormat there are other optional arguments 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

'Example using all the options
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="C:\Users\marks\Downloads\testpage2.pdf", _
    OpenAfterPublish:=False, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    Quality:=xlQualityStandard, _
    From:=1, To:=2

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, but is rarely used.



Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

6 thoughts on “VBA code to save Excel files as PDF

  1. Ali Raza says:

    sir where is save pdf print? no location found

    ‘Loop through all worksheets and save as individual PDF in same folder
    ‘as the Excel file
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ws.Name
    Next

    • Excel Off The Grid says:

      It will save all the PDF’s in the same folder as the Exce file is stored.

      If you wanted to use a specific file path then it can be defined as follows
      Filename:=”C:\Users\marks\Documents\Saved PDF.pdf”

  2. Ali Raza says:

    i have 5 sheet on excel, but this coding (Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ws.Name
    Next) working only last sheet,
    please fix this problem and if u have any example file , share with me.

  3. Stefan says:

    Hi guys,

    Can someone please help me with a VBA I’m struggling with.

    I have a button on my spreadsheet (template) that I want to use to do the following:
    -Save first page only as PDF (to specified folder in local drive C)
    -Name PDF content of Cell B7
    -Attach PDF file to Outlook mail
    -Send mail to set recipient and to recipient email address in cell B9 (this is actually what I’m struggling with).

    I would be forever grateful if someone can help me out here.

Leave a Reply

Your email address will not be published. Required fields are marked *