Excel export Excel to PDF with VBA

This Excel tutorial explains how to export Excel to PDF with and without VBA, how to export all worksheets to one PDF, and export all worksheets to separate PDF.

You may also want to read:

Select multiple worksheets using Excel VBA

How to Convert Excel to PDF (non VBA)

Excel export Excel to PDF (non-VBA)

In Excel, you can easily export Excel to PDF by navigating to File > Save As, in the Save As Type,  you can save a file as different file types. Below is the available types in Excel 2013.

excel_export_to_pdf

 

Unfortunately there is no option to save each worksheet as separate PDF in any built in options, therefore I create this post to demonstrate how to use VBA to export all worksheets to one PDF, and export all worksheets to separate PDF. If you want to know the detailed steps and what the options means, please refer to my another post- How to convert Excel to PDF.

excel_export_to_pdf_03

 

Excel VBA export Excel to PDF

In VBA, you can export Excel to PDF or XPS using ExportAsFixedFormat Method. This Method can be accessed through Workbook, Sheet, Chart, or Range.

Syntax of ExportAsFixedFormat

ExportAsFixedFormat(Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr)

Name Required/Optional Data Type Description
Type Required XlFixedFormatType xlTypePDF or xlTypeXPS.
Filename Optional Variant A string that indicates the name of the file to be saved. You can include a full path or Excel 2007 saves the file in the current folder.
Quality Optional Variant Can be set to either xlQualityStandard or xlQualityMinimum.Use xlQualityStandard if your file includes picture.
IncludeDocProperties Optional Variant Set to True to indicate that document properties should be included or set to False to indicate that they are omitted.
IgnorePrintAreas Optional Variant If set to True, ignores any print areas set when publishing. If set to False, will use the print areas set when publishing.
From Optional Variant The number of the page at which to start publishing. If this argument is omitted, publishing starts at the beginning.
To Optional Variant The number of the last page to publish. If this argument is omitted, publishing ends with the last page
OpenAfterPublish Optional Variant If set to True displays file in viewer after it is published. If set to False the file is published but not displayed.
FixedFormatExtClassPtr Optional Variant Pointer to the FixedFormatExt class.

Example 1 – export active worksheet to PDF

The below code save the active worksheet to default location. After Macro is run, PDF will automatically open.

Because I have not specified the file path for flexibility purpose, after PDF is generated and opened, choose Save As to check the path of the file saving carefully, it could be in My Document, Desktop or current Workbook folder. (I believe the location could vary depending on versions and situation)

In addition, I prefer to set Print Area manually beforehand to make sure the area I want to print is as expected.

Sub exportPDF()
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ActiveSheet.Name & ".pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=No, _
    OpenAfterPublish:=True
End Sub

Example 2 – Export workbook / all worksheets to one PDF

Replace ActiveSheet with ActiveWorkbook.

Sub exportPDF2()
    ActiveWorkbook.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ActiveWorkbook.Name & ".pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=No, _
    OpenAfterPublish:=True
End Sub

Example 3 – Export all worksheets to separate PDF

The below code exports each worksheet to each PDF. The error handler is for preventing error caused by exporting blank worksheet.

Sub exportPDF3()
    For Each sht In ActiveWorkbook.Sheets
        On Error GoTo errorhandler
        sht.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=sht.Name & ".pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=No, _
        OpenAfterPublish:=True
    Next
errorhandler:
End Sub

Example 4 – Export specific worksheets to PDF

The below code exports two separate PDF, each of them includes specific worksheets.

Sub exportPDF4()
        ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet5", "Sheet8", "Sheet12")).Select
        ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="FileA.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=No, _
        OpenAfterPublish:=True
        
        ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet6", "Sheet9", "Sheet13")).Select
        ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="FileB.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=No, _
        OpenAfterPublish:=True
End Sub

 

Outbound References

https://msdn.microsoft.com/en-us/library/bb238907%28v=office.12%29.aspx

 

Wyman W
Compensation Survey Manager@Mercer
Wyman is human resources professional, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

You may drop me a message if you are interested in HR consultancy services from Mercer.

Leave a Reply

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