How to Print to PDF in Excel VBA : With Examples and Illustrations

In this article, I’ll show you how you can print any document to PDF using VBA in Excel. We’ll use the ExportAsFixedFormat method of VBA for this purpose. I’ll discuss all the parameters of this method with proper examples and illustrations.


Print to PDF in Excel VBA (Quick View)

Print to PDF in Excel VBA


Download Practice Workbook

Download this practice workbook to practice while you are reading this article.


Introduction to the ExportAsFixedFormat Statement

⧭ Overview:

The ExportAsFixedFormat method of VBA saves any given document in the PDF format with a given name with VBA. This is a pretty useful method for those who work with a lot of Excel worksheets and store those for future use.

⧭ Syntax:

The syntax of the VBA ExportAsFixedFormat method is:

Document.ExportAsFixedFormat Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish

Print to PDF in Excel VBA

⧭ Parameters:

Parameter Required / Optional Explanation
Type Required Denotes the type of file you want to save as. Use xlTypePDF for PDF files, or xlTypeXPS for XPS files.
Filename Optional The name of the file that you want to save. Enter the full path of the file here if you want to save the file in a different path from the workbook.
Quality Optional  Denotes the quality of the file to be saved. Use xlQualityStandard for the standard quality, or xlQualityMinimum for the minimum quality.
IncludeDocProperties Optional Set it as True to include the doc properties, or set it as False not to include the doc properties.
IgnorePrintAreas Optional Set True to ignore the print areas, or False not to ignore the print areas.
From Optional The starting page number from which your document will start to be saved.
To Optional Set it as True to include the doc properties, or set it as False not to include the doc properties.
OpenAfterPublish Optional Set it as True to open the document after publishing or set it as False.

Return Value:

It converts the worksheets of an Excel workbook as a PDF document and saves it to the specified folder with the specified name.


5 Examples to Print to PDF in Excel VBA with the ExportAsFixedFormat Statement

Let’s explore a few examples of how to print a document to PDF in Excel VBA with the ExportAsFixedFormat method.


Example 1: Print to PDF in Excel VBA with No Name or Path Specified

Here we have a worksheet with the book records of a bookshop called Marin Bookstore.

Data Set to Print to PDF in Excel VBA

Let’s write a simple VBA code to convert the worksheet to a PDF document, specifying no name or path.

⧭ VBA Code:

Sub Print_To_PDF()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF

End Sub

⧭ Output:

Run this code, and you’ll find a PDF file with the same name as your workbook (Default name when no name is specified) in the same folder with your workbook (Default folder as no path is specified).

Here it’s named as Book1.pdf as the name of my workbook was Book1.

Print to PDF in Excel VBA Output

Read More: Excel VBA: How to Set Print Area Dynamically (7 Ways)


Example 2: Print to PDF in Excel VBA with Name and Path Specified

Now we’ll convert the same workbook to another PDF file specifying the name and the path.

I will save the PDF with the name “Martin Bookstore.pdf” in path C:\Users\Public\ExcelDemy on my computer. So the VBA code will be:

⧭ VBA Code:

Sub Print_To_PDF()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:="C:\Users\Public\ExcelDemy\Martin Bookstore.pdf"

End Sub

VBA Code to Print to PDF in Excel VBA

⧭ Output:

This code will save the PDF document in the path C:\Users\Public\ExcelDemy on my computer with the name Martin Bookstore.pdf.

Read More: How to Print Selected Area in Excel (2 Examples)


Similar Readings:


Example 3: Print to PDF in Excel VBA with Opening the File after Publishing

Now we’ll print the document to PDF in such a way that the file is opened after being published. We’ve to set the OpenAfterPublish parameter to True.

So the VBA code will be,

⧭ VBA Code:

Sub Print_To_PDF()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:="C:\Users\Public\ExcelDemy\Martin Bookstore.pdf", _
                                OpenAfterPublish:=True

End Sub

VBA Code to Print to PDF in Excel VBA

⧭ Output:

This code will save the PDF document in the path C:\Users\Public\ExcelDemy on my computer with the name Martin Bookstore.pdf and open the file as soon as it’s published.

Related Content: How to Set Print Preview in Excel (6 Options)


Example 4: Print Multiple Worksheets to Multiple PDF Files in Excel VBA

Up till now, we’ve printed a single worksheet. This time we’ll print multiple worksheets to multiple PDF files.

Here we’ve got a workbook with 5 worksheets, each containing the book record of a particular bookstore.

Worksheets to Print to PDF in Excel VBA

This time we’ll convert all the worksheets to PDF files.

The VBA code will be:

⧭ VBA Code:

Sub Print_Multiple_Sheets_To_PDF()

Sheet_Names = InputBox("Enter the Names of the Worksheets to Print to PDF: ")
Sheet_Names = Split(Sheet_Names, ", ")

For i = LBound(Sheet_Names) To UBound(Sheet_Names)
    Worksheets(Sheet_Names(i)).ExportAsFixedFormat Type:=xlTypePDF, _
                                                   Filename:="C:\Users\Public\ExcelDemy\" + Sheet_Names(i) + ".pdf"
Next i

End Sub

VBA Code to Print to PDF in Excel VBA

⧭ Output:

Run the code. An input box will ask you to enter the names of the worksheets to convert to PDF. Here I’ve entered Joseph Bookstore, Morgan Bookstore, Angela Bookstore.

Taking Input to Print to PDF in Excel VBA

Click OK. And it’ll save them as PDF files in the folder C:\Users\Public\ExcelDemy.

Print to PDF in Excel VBA Output

Read More: How to Print Multiple Sheets in Excel (7 Different Methods)


Example 5: Developing a User-Defined Function to Print to PDF File in Excel VBA

Finally, I’ll show you how you can develop a user-defined function to print any worksheet to PDF with Excel VBA.

Let’s develop a function called PrintToPDF that’ll print the active worksheet into a PDF file.

The VBA code will be:

⧭ VBA Code:

Function PrintToPDF()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:="C:\Users\Public\ExcelDemy\Martin Bookstore.pdf"

End Function

⧭ Output:

Enter this function in any cell of your worksheet.

=PrintToPDF()

Entering Function to Print to PDF in Excel VBA

Then click ENTER. It’ll convert the active sheet (Martin Bookstore here) to a PDF file in the specified folder.

Read More: Excel VBA: Print UserForm to Fit on a Page (2 Methods)


Things to Remember

While developing the codes, most of the time we’ve used the ActiveSheet object of VBA. It returns the worksheet that’s is active at that moment in the active workbook.

Also sometimes we’ve used the property ActiveSheet.Name. It returns the name of the active worksheet.


Conclusion

So this is the way to print any worksheet to PDF with VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo