How to Use Excel VBA to Print to PDF with a Custom File Name?

Are you looking for a way to automatically print your Excel worksheets to PDF with a custom file name? Using Excel VBA (Visual Basic for Applications), you can create a macro that will save your worksheets as PDF files with dynamic names based on cell values. In this article, we’ll walk through step-by-step how to set up a VBA macro to print to PDF and specify the PDF file name.

Why Use Excel VBA to Print to PDF?

There are several compelling reasons to leverage Excel VBA for printing to PDF:

1. Automation – You can automate the process of saving worksheets as PDFs, saving significant time and effort compared to manually printing each one. This is especially advantageous when you have a large number of sheets that need to be saved as individual PDF files.
2. Custom File Names – By using VBA, you can dynamically generate PDF file names based on worksheet content like cell values, the worksheet name, or the current date. This is much more flexible than using Excel’s built-in “Save As PDF” feature, which doesn’t allow for customized naming conventions.
3. Batch Processing – A VBA macro can loop through all the worksheets in your workbook and save each one to a separate PDF, allowing you to print multiple sheets at once. This batch processing capability is a huge efficiency booster when dealing with sizeable workbooks.
4. No Printing Dialog – When you print to PDF using VBA, the printing dialog box doesn’t appear, making the process seamless and free from manual intervention. The macro can run in the background without disrupting your other work.
5. Consistency – Using a macro ensures that your PDFs are generated consistently each time, with the same settings, formatting, and naming scheme. This standardization can be difficult to achieve with manual printing.

So let’s look at how to actually implement printing to PDF with custom file names using VBA in Excel.

Step 1: Enable the Developer Tab in Excel

To create an Excel VBA macro, you first need to enable the Developer tab in the ribbon if it’s not already visible. Here’s how:

1. Go to File > Options
2. Click on Customize Ribbon
3. Under Main Tabs, check the box next to Developer
4. Click OK to close the Excel Options dialog

You should now see the Developer tab appear in the ribbon. This tab contains tools for working with macros and other advanced Excel features.

Step 2: Open the Visual Basic Editor

With the Developer tab enabled, you can now open the Visual Basic Editor to start writing your macro:

1. Go to the Developer tab
2. Click the Visual Basic button (or press Alt+F11)

The Visual Basic Editor will open in a new window. This is the integrated development environment (IDE) where you’ll write the VBA code for your print to PDF macro.

Step 3: Create a New Module

In the Visual Basic Editor, you need to insert a new module to hold your macro code:

1. In the Project pane on the left, find your workbook
2. Right-click on the workbook and select Insert > Module

A new module will be created where you can type your print to PDF VBA code. Modules are containers for related pieces of code, and help keep your project organized.

Step 4: Write the Print to PDF Macro Code

Now you’re ready to write the actual VBA macro to print your worksheet to a PDF. Here’s the code to use:

Sub PrintWorksheetToPDF() 
    Dim wsA As Worksheet
    Dim wbA As Workbook
    Dim strTime As String
    Dim strName As String
    Dim strPath As String
    Dim strFile As String 
    Dim strPathFile As String
    Dim myFile As Variant

    Set wbA = ActiveWorkbook
    Set wsA = ActiveSheet

    strTime = Format(Now(), "yyyymmdd\_hhmm")

    'Get the name from cell A1
    strName = wsA.Range("A1").Value

    'Target folder, use current workbook folder as default
    strPath = wbA.Path

    If strPath = "" Then
        strPath = Application.DefaultFilePath 
    End If

    strPath = strPath & "\"

    'Create PDF file name
    strFile = strName & "_" & strTime & ".pdf"
    strPathFile = strPath & strFile

    myFile = Application.GetSaveAsFilename _
        (InitialFileName:=strPathFile, _
            FileFilter:="PDF Files (*.pdf), *.pdf", _ 
                    Title:="Select Folder and FileName to save")

    If myFile <> "False" Then
        wsA.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _ 
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        'confirmation message 
        MsgBox "Your PDF file has been created: " _
            & vbCrLf _
            & myFile
    End If

End Sub

Here’s a breakdown of what this code does:

1. Declares variables to hold the active workbook, worksheet, PDF file path, and file name. Using clear variable names makes the code more readable.
2. Gets the current date and time to use in the file name. This is done using the VBA Format and Now functions.
3. Retrieves a name value from cell A1 in the active worksheet. You can change this cell reference to pull the name from elsewhere.
4. Sets the target folder to the current workbook’s folder. If the workbook isn’t saved, it uses the application’s default file path.
5. Creates the PDF file name by combining the name from A1, a date/time stamp, and the “.pdf” extension.
6. Prompts the user to select a folder and file name to save the PDF using the GetSaveAsFilename method. This displays a standard “Save As” dialog box.
7. Exports the active worksheet as a PDF using the specified file path and name. The ExportAsFixedFormat method is used for this.
8. Displays a message box confirming the PDF was created, showing the full file path.

You can modify parts of this code, such as the location it pulls the PDF file name from (currently cell A1) or additional details to include in the file name.

Step 5: Run the Print to PDF Macro

To run your new macro and print your active worksheet to a PDF:

1. Go back to the Excel workbook
2. Make sure the worksheet you want to print to PDF is active
3. Go to the Developer tab
4. Click Macros
5. Select your PrintWorksheetToPDF macro and click Run

The macro will run, prompting you to select a location and file name to save the PDF. After saving, a message box will confirm the PDF file creation.

Alternatively, you can run the macro directly from the Visual Basic Editor by clicking the green “play” button or pressing F5.

And that’s it! You’ve now created an Excel VBA macro to automatically print your worksheet to a PDF with a custom file name. This technique can save you significant time and effort when you need to generate PDF versions of your Excel worksheets.

Tips for Using the Print to PDF Excel Macro

Here are a few suggestions to get the most out of this print to PDF automation:

• Customize the cell reference where the macro pulls the PDF name from based on your worksheet layout. You can use any cell or combination of cells to build the file name.
• Modify the name format to include other useful details like the workbook name, worksheet name, date, username, etc. This can make it easier to keep track of your generated PDFs later.
• Use the macro in combination with other VBA code to loop through multiple worksheets in your workbook and save each to its own PDF. This is a big time-saver if you need to print every worksheet at once.
• Add error handling to your macro code to gracefully handle issues like invalid sheet names, write-protected destination folders, etc. This will make your macro more robust and reliable.
• Consider adding a button to run the macro so you don’t need to open the Developer tab each time. You can insert a button and assign the macro to run when clicked.
• If you frequently use this macro, save it in your Personal Macro Workbook so it’s available in all your Excel files. This saves you from recreating it in each workbook.

Advanced Techniques

Once you’ve mastered the basics of printing to PDF with VBA, there are many ways to extend the technique, such as:

• Automatically emailing the generated PDF using VBA code to open a new email message, attach the PDF, and send it to a specified recipient
• Saving PDFs to a SharePoint document library or other cloud location by mapping a network drive with VBA and using that as the destination folder
• Encrypting and password-protecting the PDFs for added security using the ExportAsFixedFormat method parameters
• Merging multiple worksheets into a single PDF by exporting each sheet to a temporary PDF file and then using a PDF library to combine them into one document

The possibilities are nearly endless once you understand the core concepts of programmatically exporting Excel sheets to PDF format with VBA.

Final Thoughts

Printing your Excel worksheets to PDF with custom file names is easy with a VBA macro. The code provided here will prompt you for a save location, pull a name value from cell A1, combine it with a date/time stamp, and save the active sheet as a PDF.

With a few tweaks, you can adapt this technique to automatically generate PDF versions of your Excel sheets just the way you need. You can batch print multiple sheets, create standardized naming conventions, email the PDFs, and much more.

Implementing an Excel print to PDF macro is a great way to automate repetitive tasks, ensure consistency, and save significant time and effort. Give it a try and see how it can streamline your workflows! Let me know if you have any other questions.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.