How to Print to PDF Using VBA (With Example)

by Zach Bobbitt Published on April 5, 2023

You can use the following syntax in VBA to print the currently active Excel sheet to a PDF:

Sub PrintToPDF()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:="my_data.pdf", _
                                Quality:=xlQualityStandard, _
                                IncludeDocProperties:=False, _
                                IgnorePrintAreas:=False, _
                                OpenAfterPublish:=True

End Sub

This particular macro will print the currently active Excel sheet to a PDF called my_data.pdf and it will be saved in the current folder.

Note #1: You can also include a full file path in the Filename argument to save the PDF to a specific folder.

Note #2: The line OpenAfterPublish:= True tells VBA to open the PDF as soon as it is exported. You can leave out this argument if you don’t want the PDF to be opened after exporting.

Note #3: The only required argument in the ExportAsFixedFormat method is Type, which must be set to xlTypePDF to print the sheet to a PDF format.

The following example shows how to use this macro in practice.

Example: Export Excel Sheet to PDF Using VBA

Suppose we have the following Excel sheet that contains information about various basketball players:

Now suppose that we would like to export this sheet to a PDF called my_data.pdf.

We can create the following macro to do so:

Sub PrintToPDF()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:="my_data.pdf", _
                                Quality:=xlQualityStandard, _
                                IncludeDocProperties:=False, _
                                IgnorePrintAreas:=False, _
                                OpenAfterPublish:=True

End Sub

When we run this macro, the Excel sheet is exported to a PDF and then the PDF is automatically opened:

Note that the exact formatting of the cells with the borders and the fill color is included in the PDF.

Note: You can find the complete documentation for the ExportAsFixedFormat method in VBA here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Create Folders
VBA: How to Delete Folders
VBA: How to Delete Files

Zach Bobbitt

Hey there. My name is Zach Bobbitt. I have a Masters of Science degree in Applied Statistics and I’ve worked on machine learning algorithms for professional businesses in both healthcare and retail. I’m passionate about statistics, machine learning, and data visualization and I created Statology to be a resource for both students and teachers alike. My goal with this site is to help you learn statistics through using simple terms, plenty of real-world examples, and helpful illustrations.

3 Replies to “How to Print to PDF Using VBA (With Example)”

Ben says:

February 20, 2024 at 2:39 pm

How do I force it to open in adobe instead of sharepoint?

Reply
Andrew Bonczyk says:

September 20, 2024 at 12:31 pm

Hi Zach,

Thanks for this. Just wondering what the code would look like if I am trying to point the PDF to a specific folder.

Reply
1. James Carmichael says:
  
  September 21, 2024 at 4:23 pm
  
  Hi Andrew…To print an Excel sheet to a PDF and save it to a specific folder using VBA, you can use the `ExportAsFixedFormat` method, which allows you to export the active sheet (or any sheet you specify) as a PDF.
  
  Here’s an example VBA code to print to PDF and save it to a specific folder:
  
  ### VBA Code Example:
  
  “`vba
  Sub PrintToPDF()
  Dim ws As Worksheet
  Dim filePath As String
  Dim fileName As String
  Dim fullFilePath As String
  
  ‘ Set your worksheet (ActiveSheet or any other specific sheet)
  Set ws = ThisWorkbook.Sheets(“Sheet1”) ‘ Change “Sheet1” to the name of your sheet
  
  ‘ Set the folder path where you want to save the PDF
  filePath = “C:\YourFolder\SubFolder\” ‘ Change this to your desired folder path
  
  ‘ Set the PDF file name
  fileName = “MyExportedFile.pdf” ‘ You can generate the file name dynamically if needed
  
  ‘ Combine the folder path and file name
  fullFilePath = filePath & fileName
  
  ‘ Export the worksheet as PDF
  ws.ExportAsFixedFormat Type:=xlTypePDF, _
  Filename:=fullFilePath, _
  Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, _
  OpenAfterPublish:=False
  
  MsgBox “PDF successfully saved to ” & fullFilePath
  
  End Sub
  “`
  
  ### Key Points:
  – **`filePath`**: Change the folder path to the desired directory where you want to save the PDF.
  – **`fileName`**: Set the file name for the PDF. You can also generate this dynamically (e.g., based on date or other variables).
  – **`ExportAsFixedFormat`**: This method prints the worksheet to PDF with options for print quality, document properties, and whether to open the PDF after exporting.
  
  You can adapt the code to suit your specific needs. Let me know if you need further customization!
  
  Reply