Table of Contents
Printing to PDF using VBA is a simple task that can be accomplished using the PrintOut method of the ActiveSheet object. This method can be utilized with a simple code example that will allow the user to print the active sheet to a PDF file. This code example also allows the user to specify the name of the PDF file, its location, and the page range to be printed. This makes it easier for the user to easily print a specific range of pages to a PDF file.
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 .