[Excel VBA] How to Export a Sheet as a PDF File

When sharing Excel materials with others or saving them as official records, it is common to convert them to PDF format. This ensures the layout remains intact and allows anyone to open the file.

While you can convert files to PDF manually, using VBA allows you to automate this operation with a single button press.

In this article, I will explain the basics of using the .ExportAsFixedFormat method to export worksheets or specific cell ranges as PDF files using VBA.

Completed VBA Code

Below is the VBA code to output the sheet named “Sheet1” as a PDF with specific settings.

Sub SaveSheetAsPDF()

    ' Declare variables
    Dim targetSheet As Worksheet
    Dim outputFilePath As String

    ' Set the target sheet
    Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
    
    ' Determine the PDF file path (Save in the same folder as the Excel file, using the sheet name)
    outputFilePath = ThisWorkbook.Path & "\" & targetSheet.Name & "_Report.pdf"

    ' Export the sheet as PDF
    targetSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=outputFilePath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

End Sub

Explanation of the ExportAsFixedFormat Method

.ExportAsFixedFormat is a method used to output sheets or cell ranges in PDF or XPS format. It has many arguments, but here I will introduce the most important ones.

1. Type Argument (Required)

Specifies the output file format.

• xlTypePDF: Outputs in PDF format. (Usually, this is used)
• xlTypeXPS: Outputs in XPS format.

2. Filename Argument (Required)

Specifies the full path of the file to be saved as a string. Using ThisWorkbook.Path is convenient because it allows you to specify the same folder as the macro-enabled Excel file. Filename:=ThisWorkbook.Path & "\OutputFileName.pdf"

3. Quality Argument (Optional)

Specifies the quality of the PDF.

• xlQualityStandard (Default): Standard quality.
• xlQualityMinimum: Minimum quality. The file size becomes smaller, but image resolution decreases.

4. IgnorePrintAreas Argument (Optional)

Specifies whether to ignore print areas using True or False.

• False (Default): If a print area is set on the sheet, only that area is converted to PDF.
• True: Ignores the set print area and converts the entire sheet to PDF.

5. OpenAfterPublish Argument (Optional)

Specifies whether to automatically open the file after creating the PDF using True or False.

• True: The file opens automatically in the default PDF viewer after creation. This is useful for checking the content immediately.
• False (Default): The file is only saved and does not open automatically.

How to Export Only a Specific Cell Range as PDF

If you want to output only a specific cell range as a PDF instead of the entire sheet, execute the .ExportAsFixedFormat method on a Range object instead of a Worksheet object.

Sub SaveRangeAsPDF()

    ' Declare variables
    Dim targetRange As Range
    
    ' Specify the cell range to export as PDF
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("B2:G30")
    
    ' Export only the specified range as PDF
    targetRange.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ThisWorkbook.Path & "\Range_Export.pdf", _
        OpenAfterPublish:=True
        
End Sub

As shown above, you can easily limit the output scope simply by changing the target from Worksheet to Range.

Summary

The foundation of creating PDFs in Excel VBA is the .ExportAsFixedFormat method.

• Whole Sheet: Call the method from a Worksheet object to convert the entire sheet (or print area) to PDF.
• Specific Range: Call the method from a Range object to convert only specific cells to PDF.
• Automation: Utilize optional arguments like OpenAfterPublish to create more convenient automation.

By mastering this method, you can complete routine document issuance processes, such as creating invoices or reports, with a single button.