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 example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: 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
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.
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.
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- 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.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: