Excel VBA to Print As PDF and Save with Automatic File Name

There are benefits to exporting an Excel spreadsheet as a PDF document. By changing the file to PDF format, we will be able to print and share it with various professional connections. PDFs are a trustworthy standard for exporting documents that you feel the need to share with others or publicize. In this article, we will demonstrate some examples of VBA to print as PDF and save with an automatic file name.

Excel VBA to Print As PDF and Save with Automatic File Name: 9 Examples

We can easily print an Excel file as a PDF and save the file with an automatic file name, using the Excel toolbar. But, it would be easier with Excel VBA. We just need the VBA code and run them. We don’t need so many clicks for completing the task and this saves our time.

Visual Basic for Applications (VBA) is a programming model and isolated program that is most commonly seen in Microsoft Office. It’s an analytical tool, often available as Excel add-ins, that optimizes manual operations such as monotonous, time-consuming chores. It also can generate CSV files. So let’s see some examples to print the excel file as a PDF with the automatic file name.

1. Print Workbook to PDF & Save File Name Automatically in Excel

Suppose, we want to print the whole workbook and save the file name as we put the name on our code. Now, assume that we want to save a PDF file on our computer Local Disk (E:). As we can see in the picture below that the location does not contain any pdf files. After running the VBA code, we will be able to see our desired PDF file in that location on our PC.

With Excel VBA, users can easily use the code which acts as excel menus from the ribbon. To use the VBA code to print pdf and save with an automatic file name, let’s follow the procedure.

STEPS:

• Firstly, go to the Developer tab from the ribbon.
• Secondly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.

• Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.

• This will appear in the Visual Basic Editor where we write our codes to create a table from range.
• Thirdly, click on Module from the Insert drop-down menu bar.

• This will create a Module in your workbook.
• And, copy and paste the VBA code shown below.

VBA Code:

Sub Print_Workbook()
Dim loc As String
loc = "E:\Workbook.pdf"
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=loc
End Sub

• Run the code by pressing the F5 key on your keyboard.

• Finally, you can see that a PDF file name, Workbook is now located on that path on your computer. So, that means the file name is automatically saved.

• And, lastly, if you go back to your workbook, you can see some dotted lines. This is because the file is now ready for printing.

Sub Print_Workbook()

loc = "E:\Workbook.pdf" 

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, filename:=loc

End Sub

2. Save Active Worksheet Automatically as PDF

Let’s see another example print an active sheet to a pdf and save the file name automatically using Excel VBA.

STEPS:

• First, go to the Developer tab from the ribbon.
• Second, click on Visual Basic to open the Visual Basic Editor.
• Another way to open the Visual Basic Editor is simply to press Alt + F11.
• Or, right-click on the sheet, then select View Code.
• Next, go to Insert and select Module from the drop-down menu.
• And, this will open up the visual basic window.
• After that, copy and paste the VBA code below.

VBA Code:

Sub Print_Sheet()
Dim loc As String
loc = "E:\Worksheet.pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=loc
End Sub

• Further, press the F5 key or click on the Run Sub button to run the code.

• Likewise the previous example, the file is saved as a PDF with the automatic file name.

If you read example1’s code explanation, you will understand this too.

3. Print PDF File from Excel with VBA in Range

Let’s look at another example of utilizing Excel VBA to print an active sheet to pdf and save the file name automatically.

STEPS:

• To begin, click the Developer tab on the ribbon.
• Second, launch the Visual Basic Editor by clicking on Visual Basic.
• Alternatively, you may access the Visual Basic Editor by pressing Alt + F11.
• Or, right-click on the sheet and choose View Code from the menu.
• Next, pick the Module from the drop-down box under Insert.
• And the visual basic window will appear.
• Write the code there.

VBA Code:

Sub PrntPDF()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
Collate:=True, ActivePrinter:="Adobe PDF"
Dim fnam As String
fnam = Range("B4").Value
End Sub

• Finally, press the F5 key to run the code.

• Afterward, you can see that a PDF file with the name Workbook has been added to that location on your computer. As a result, the file name is automatically preserved.

Read More: Print Range to PDF with VBA in Excel

4. Excel VBA to Loop Across Selected Sheet and Print PDF

Let’s have a glance at another way to print to PDF and save automatically the file name.

STEPS:

• To start, open the ribbon and select the Developer option.
• Then, to access the Visual Basic Editor, click on Visual Basic.
• Pressing Alt + F11 will also bring up the Visual Basic Editor.
• Alternatively, right-click the sheet and choose View Code from the menu that appears.
• Now, from the Insert drop-down option, pick Module.
• Then copy and paste the VBA code that follows.

VBA Code:

Sub PrntPDF1()
Dim wrksht As Worksheet
Dim sht As Variant
Set sht = ActiveWindow.SelectedSheets
For Each wrksht In sht
wrksht.Select
wrksht.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=ThisWorkbook.Path & "/" & wrksht.Name & ".pdf"
Next wrksht
sht.Select
End Sub

• Run the code by pressing the F5 key.

• Eventually, you can see that a Workbook PDF file has been uploaded to that area on your computer. As a consequence, the file name is kept automatically.

This will save the file as the sheet number of the workbook.

For Each wrksht In sht
    wrksht.Select
    wrksht.ExportAsFixedFormat Type:=xlTypePDF, filename:=ThisWorkbook.Path & "/" & wrksht.Name & ".pdf"
Next wrksht

5. Print to PDF and Save the File Name Instinctively in Excel

Now, take a look at another Excel VBA method for saving excel files to pdf and name the file automatic system.

STEPS:

• To begin, open the ribbon and choose Developer from the drop-down menu.
• Then, select Visual Basic to open the Visual Basic Editor.
• The Visual Basic Editor may also be accessed by pressing Alt + F11.
• Alternatively, you may right-click the sheet and select View Code from the pop-up menu.
• After that, select Module from the Insert drop-down menu.
• Further, copy and paste the following VBA code.

VBA Code:

Sub PrntPDF2()
Dim loc As String
loc = "E:\Sheet6.pdf"
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=loc, _
OpenAfterPublish:=False, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
Quality:=xlQualityStandard, _
From:=1, To:=2
End Sub

• Finally, run the code by pressing F5 on your keyboard and you see the result.

• You will subsequently see that a Workbook PDF file was already saved to that location on your PC. As a result, the file name is kept automatically.

Similarly, as in the earlier example, this will also save the pdf file as the sheet number.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=loc, OpenAfterPublish:=False, IncludeDocProperties:=True, IgnorePrintAreas:=False, Quality:=xlQualityStandard, From:=1, To:=2

6. VBA Function to Print PDF and Save File Name Automatically

Let’s explore another Excel VBA way of printing to PDF and saving the filename automatically. We will use a function and save the file to PDF in this example. We also use the Msgbox to give us a message whether the file is saved or not.

STEPS:

• In the beginning, go to the Developer tab > Visual Basic > Insert > Module.
• Or, right-clicking on the worksheet will open up a window. From there go to the View Code.
• And, this will take you to the Visual Basic Editor field, where we can write VBA Macros.
• On the other hand, pressing Alt + F11 will also open the Visual Basic Editor.
• After that, type the VBA code.

VBA Code: 

Sub PrntPDF3()
Dim wrks As Worksheet
Dim wrkb As Workbook
Dim snam As String
Dim sloc As String
Dim sf As String
Dim slocf As String
Dim myFile As Variant
Dim l As Long
On Error GoTo errHandler
Set wrkb = ActiveWorkbook
Set wrks = ActiveSheet
sloc = wrkb.Path
If sloc = "" Then
    sloc = Application.DefaultFilePath
End If
sloc = sloc & "\"
snam = wrks.Range("A1").Value & " Print " & wrks.Range("A2").Value & " PDF " & wrks.Range("A3").Value
sf = snam & ".pdf"
slocf = sloc & sf
If PrintFile(slocf) Then
    l = MsgBox(vbQuestion + vbYesNo, "File Exists")
    If l <> vbYes Then
        myFile = Application.GetSaveAsFilename(InitialFileName:=slocf, FileFilter:="PDF Files (*.pdf), *.pdf", _
                 Title:="Save File Name")
        If myFile <> "False" Then
            slocf = myFile
        Else
            GoTo exitHandler
        End If
    End If
End If
wrks.ExportAsFixedFormat Type:=xlTypePDF, filename:=slocf, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
MsgBox "Print PDF: " & vbCrLf & strPathFile
exitHandler:
Exit Sub
errHandler:
MsgBox "Not Print"
Resume exitHandler
End Sub
Function PrintFile(rsFullPath As String) As Boolean
PrintFile = CBool(Len(Dir$(rsFullPath)) > 0)
End Function

• And, run the code to see the result by pressing the F5 key.

• This will appear in Msgbox and make sure that the PDF file is now ready for printing.

• Similarly, as before, you will notice that a Workbook PDF file has already been saved to that location on your computer. As a result, the file name is preserved by default. As we set the file name Print PDF, it saved the file name Print PDF.

If you look at the previous code’s explanation you will understand the lines of the code properly. You don’t need to change the code, just change the ranges as per your preferences. You can copy the code and use it for your working purpose.

7. Excel VBA Code to Print to PDF And Save the File Name Automatically

Let’s look at another Excel VBA method for printing to PDF and automatically storing the filename.

STEPS:

• To begin, navigate to the Developer tab on the ribbon.
• Second, under the Code section, select Visual Basic to launch the Visual Basic Editor. To open the Visual Basic Editor, click Alt + F11.
• Alternatively, you can just right-click on your worksheet and select View Code. This will take you to the Visual Basic Editor as well.
• This will be displayed in the Visual Basic Editor, where we will write the code to generate a table from a range.
• Third, from the Insert drop-down menu bar, select Module.
• And, copy and paste the VBA code shown below.

VBA Code:

Sub PrintPDF4()
Dim wrksht As Worksheet
Dim wrkbk As Workbook
Dim snam As String
Dim sloc As String
Dim sf As String
Dim slocf As String
Dim file As Variant
On Error GoTo errHandler
Set wrkbk = ActiveWorkbook
Set wrksht = ActiveSheet
sloc = wrkbk.Path
If sloc = "" Then
    sloc = Application.DefaultFilePath
End If
sloc = sloc & "\"
snam = wrksht.Range("A1").Value & " - " & wrksht.Range("A2").Value & " - " & wrksht.Range("A3").Value
sf = snam & ".pdf"
slocf = sloc & sf
wrksht.ExportAsFixedFormat Type:=xlTypePDF, filename:=slocf, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
MsgBox "Print PDF: " & vbCrLf & strPathFile
exitHandler:
Exit Sub
errHandler:
MsgBox "Not Print"
Resume exitHandler
End Sub

• Then, execute the code by clicking the RubSub button or hitting the F5 keyboard shortcut.

• Particularly, then, a Msgbox will appear.

• The file is saved as a PDF with the same automated file name as in the previous example.

sloc = wrkbk.Path
If sloc = "" Then
sloc = Application.DefaultFilePath
End If
sloc = sloc & "\"
snam = wrksht.Range("A1").Value & " - " & wrksht.Range("A2").Value & " - " & wrksht.Range("A3").Value

sf = snam & ".pdf"
slocf = sloc & sf

wrksht.ExportAsFixedFormat Type:=xlTypePDF, filename:=slocf, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

MsgBox "Print PDF: " & vbCrLf & strPathFile
exitHandler:
Exit Sub
errHandler:
MsgBox "Not Print"
Resume exitHandler

8. Print a Specific Excel Sheet with Automatic File Name

Let’s look at a different Excel VBA method for printing to PDF and automatically storing the file name.

STEPS:

• In the first place, select the Developer tab from the ribbon.
• Second, under the Code category, select Visual Basic to launch the Visual Basic Editor. Alternatively, press Alt + F11 to launch the Visual Basic Editor.
• Instead, right-click on your worksheet and select View Code.
• This will display in the Visual Basic Editor, where we will write our code to generate a table from a range.
• Thirdly, select Module from the Insert drop-down menu bar.
• Further, copy and paste the VBA code below.

VBA Code:

Sub PrintPDF5()
Dim loc As String
Dim r As Range
loc = "E:\PDF File.pdf"
Set rng = Sheets("IT").Range("A1:F13")
rng.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=loc
End Sub

• Furthermore, run the code by clicking the RubSub button or using the F5 keyboard shortcut.

• This file is saved as a PDF with the very same name as the previous example.

Dim loc As String
Dim r As Range
loc = "E:\PDF File.pdf"
Set rng = Sheets("IT").Range("A1:F13")

rng.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=loc

9. Save File Name in an Automatic Way While Printing to PDF in Excel VBA

Let’s have a look at another Excel VBA method for printing to PDF and automatically storing the filename.

STEPS:

• To begin with, select the Developer tab from the ribbon.
• Second, pick Visual Basic from the Code area to open the Visual Basic Editor. Click Alt + F11 to launch the Visual Basic Editor.
• You may also right-click on your worksheet and select View Code. This will also take you to the Visual Basic Editor.
• Now, we can see the Visual Basic Editor, where we will write the code to create a table from a range.
• Further, select Module from the Insert drop-down menu bar.
• Then, copy and paste the VBA code that follows.

VBA Code:

Sub Prnt_PDF()
    Call Automatic_Name
End Sub
Function Automatic_Name() As Boolean  ' Copies sheets into new PDF file for e-mailing
    Dim sht As String, file As String, loc As String
    Dim s As String
Application.ScreenUpdating = False
    sht = ActiveSheet.Name
    file = ActiveWorkbook.Name
    loc = ActiveWorkbook.Path
    s = loc & "\" & sht & ".pdf"
    On Error Resume Next
    ActiveSheet.PageSetup.PrintQuality = 600
    err.Clear
    On Error GoTo 0
    On Error GoTo RefLibError
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=s, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
    On Error GoTo 0
SaveOnly:
    MsgBox "Saved as .pdf  file: " & vbCrLf & vbCrLf & SvAs & _
    "Review the .pdf document."
    Automatic_Name = True
    GoTo EndMacro
RefLibError:
    MsgBox "Unable to save as PDF."
    Automatioc_Name = False
    EndMacro:
End Function

• The code will then execute by clicking the RubSub button or using the F5 keyboard shortcut.

sht = ActiveSheet.Name
file = ActiveWorkbook.Name
loc = ActiveWorkbook.Path
s = loc & "\" & sht & ".pdf"

On Error Resume Next
ActiveSheet.PageSetup.PrintQuality = 600
err.Clear
On Error GoTo 0

On Error GoTo RefLibError
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=s, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
On Error GoTo 0
SaveOnly:
MsgBox "Saved as .pdf  file: " & vbCrLf & vbCrLf & SvAs & _
"Review the .pdf document."
Automatic_Name = True
GoTo EndMacro
RefLibError:
MsgBox "Unable to save as PDF."
Automatioc_Name = False

Download Practice Workbook

You can download the workbook and practice with them.

Conclusion

The above methods will assist you to do that work from Print to PDF and Save Automatic File Name in Excel VBA. I hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.

Related Articles

• Print to PDF Using Macro Button in Excel
• Excel VBA: ExportAsFixedFormat PDF with Fit to Page
• How to Print to PDF in Excel VBA : With Examples and Illustrations
• Print Multiple Excel Sheets to Single PDF File with VBA