Save Excel as PDF with VBA

VBA Code Snippets

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

Download Icon

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

Generate accurate VBA code in seconds with AutoMacro

AutoMacroExample

AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.

Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.

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.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

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

20 thoughts on “Save Excel as PDF with VBA

  1. Allan Andersen says:

    I Bought Office 365 Personal that I expected tha VBA still was in Excel, but I cannot find. If it not there(As far I can See) I must of course pay for 1 year and then STOP. It there another way to get VBA with Excel in a another product.
    I am very very disappointed, because it was the only reason I bought this product.

    • Excel Off The Grid says:

      To use VBA, you need to turn on the Developer Tab:

      • Right-click on a space in the ribbon -> Click Customize Ribbon
      • Ensure the Developer options is clicked
      • Click OK
    • Excel Off The Grid says:

      Hi Frede

      ActiveWorkbook.Name would give you the name of the workbook. But you would also get the “.xlsx” on the end. So something like this would give you a PDF file name:

      Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".")) & "pdf"
  2. C'Niche says:

    Cannnot find ANYWHERE that can tell me how to SELECT SPECIFIC WORKSHEETS and create MULTIPLE PDFs. Can you?

  3. Patridk Murphy says:

    Good beginner article.
    It would be great to see you take this further and actually manipulate the pdf after generating it. Things like adding a menu, or bookmarks, modifying the file security, adding attachments to the pdf file, etc. are big winners.

    In my own experience this can be done but it requires the use of the Microsoft_JScript library and PDF object.

    Let me know if you take this on.

  4. Matt says:

    Thanks this has helped me save a ton of time!
    I have one question:

    I want the file name of my sheet to be a cell value (the invoice number) which I can do with :

    s = Range(“G6”).Value


    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, Filename:=s, …..etc….

    BUT…
    The pdf goes to the location where the spreadsheet is. I would like it to go to a different folder. Is this possible?

    • Excel Off The Grid says:

      Read the section in the article entitled “Notes for saving PDF documents”, this explains how to save in different locations.

      For your scenario:

      You either need to add the full file path into G6 our you could concatenate together such as (assuming the folder path is in G7):

      s = Range("G7").Value & Range("G6").Value
      • Matt says:

        Brilliant; that was exactly the fix. I put the file path in another cell and used the concatenate you suggested.

        THANK YOU SO MUCH!!

  5. Jitender Modgil says:

    i am using win10 64 bit
    but error on

    Selection.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

    on

    • Excel Off The Grid says:

      I don’t believe there is any reason why Windows 10 64 bit would behave like that. Is the saveLocation valid? That’s the most likely cause.

  6. Lewis says:

    Thanks for the article, I do have one question. When I convert the spreadsheet to a PDF some of the sheets are oversized and are stretched across multiple pages. Is there a way to adjust the zoom before converting? Thank you.

    • Excel Off The Grid says:

      I think it’s based upon how the print settings have been applied, adjust the settings in the Page Layout menu, Page Setup and Scale to Fit sections.

Leave a Reply

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