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.
