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

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.

12 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.

  4. Ted says:

    Don’t know if you can help with VBA code for a macro but I am using Excel for MAC . Any help, suggestions appreciated. Do I need any special add-ins to make this work?
    This is what I want to do:-

    Select RangeA2 to I44
    Use the filename that’s in Cell H10
    Save as PDF to Path DATA1\DATA\DROPBOX\\BUSINESS\PDFINVOICE Filename as in Cell H10)

    Much apprecited

  5. Abison says:

    Hi,

    Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Dim FilePath As String
    Dim filename As String
    FilePath = “D:\fileName ”
    filename = FilePath & Format(Date, “DD.MM.YY”)

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=filename

    End Sub

    The above code worked fine for me while I was working in Excel 2013 in Windows 7.
    Last month I upgraded my Windows from 7 to 10 last month and since then I am encountering – Run-time error ‘1004’

    I am still using Excel 2013.

    How do I solve this ?
    Why am I getting this error now ?

    Thanks in advance guys.

  6. Atheer Aljuboory says:

    Sir,

    I need to print multiple file into a single PDF file. Is this possible?

    What I am doing is that I fill in a worksheet, print as pdf and then fill the sheet again with different information and append it to the same pdf file. In this way, I will have only one pdf file for all my sheets. Is this something I can do with this function? any other suggestion please?

    Thanks,
    Atheer

    • Excel Off The Grid says:

      This method will not provide exactly what you need.

      You’ll need to write a macro that hard-codes the result to a new worksheet (or pastes as picture) for each parameter. Once all that has looped through, you can then save all the new worksheets as a single PDF. Finally, delete the new worksheets created.

      Alternatively, if you have full Adobe Acrobat, you can merge PDFs using the Adobe reference library. Just check on Google, there are codes on various other sites.

Leave a Reply

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