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

Method 1 – Print to PDF in Excel VBA with No Name or Path Specified

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

Data Set to Print to PDF in Excel VBA

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).

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

Print to PDF in Excel VBA Output


Method 2 – Print to PDF in Excel VBA with Name and Path Specified

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.


Method 3 – Print to PDF in Excel VBA with Opening the File after Publishing

Print the document to PDF in such a way that the file is opened after being published. 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.


Method 4 – Print Multiple Worksheets to Multiple PDF Files in Excel VBA

We printed a single worksheet. This time we’ll print multiple worksheets to multiple PDF files.

We got a workbook with 5 worksheets, each containing the book record of a particular bookstore.

Worksheets to Print to PDF in Excel VBA

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. We 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


Method 5 – Developing a User-Defined Function to Print to PDF File in Excel VBA

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

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


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.


Download Practice Workbook

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


Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo