Contextures

Excel VBA - Save As PDF Files

In Excel 2010 and later, you can export a sheet, or a group of sheets, as a PDF file. This tutorial shows sample code for doing this.

Excel VBA Master Class: Do your Excel programming skills need a boost? Are you wasting valuable time when you write and troubleshoot your code? Get expert training at the Excel VBA Master Class that will be held in Amsterdam, on October 26-27. This two-day course, led by Jan Karel Pieterse and Tony de Jonker, should quickly pay for itself, in the time that you'll save on your VBA projects.

Export Active Sheet as PDF File

The following macro code will export the active sheet (or sheets) in PDF format. Copy the code to a regular code module, then select the sheet(s) you want to export, and run the macro.

See the section further down, for details on how the macro works.

run an Excel macro

The Export As PDF Code

Copy the code to a regular code module, then select the sheet(s) you want to export, and run the macro.

Sub PDFActiveSheet()
'www.contextures.com
'for Excel 2010 and later
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
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

How The Macro Works

Before you run the macro, select the sheet(s) that you want to export to the PDF file.

When the macro starts, it sets variables for the active sheet, and the active workbook. Those will be used to set the default file name and folder.

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

A time stamp will be added to the default name, in the format yyyymmdd_hhmm.

In the format string shown below, a backslash is entered before the underscore, to indicate it is a literal character. Otherwise, Excel would interpret the underscore as the spacing character that is used in Excel number formatting.

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

Next, the macro gets the default path for saving the PDF file. If the active workbook has been saved, its path is used. If the active workbook has not been saved, Excel's default save folder is used.

strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

The name of the active sheet is cleaned up -- spaces are removed, and periods are replaced with underscores.

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

The file path, revised sheet name, and the ".pdf" extension are combined.

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

The Save As dialog box opens, with the current folder selected, or the default save folder. The folder is filtered, to show only the PDF files that it contains.

At the top of the Save As window, the customized title is shown, "Select Folder and FileName to save"

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

The default file name is filled in, and you can overwrite it, to save the file with a different name. You can also select another folder --just browse to a different location.

Save As window with default name

Then, click the Save button, or click Cancel, if you change your mind.

  • If you click Cancel, the value of myFile is "False", and nothing more happens -- the macro ends.
  • If you click Save, the PDF file is created.
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

Then, if the file was created, the macro shows a confirmation message with the file path and name.

    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile

Click the OK button to close the message box.

Confirmation Message

Download the Sample File

To see how the macro works, you can download the Export Excel Sheet as PDF sample file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros, if you want to run the macro.

More Tutorials

Send emails from Excel with PDF attachment

Copy Macro Code to a workbook

Excel VBA Edit Your Recorded Macro

Excel VBA Getting Started  

 

 

 

 

Excel UserForms for Data Entry

 

 

excel tools

 

 

 



Last updated: September 15, 2016 10:57 AM