Excelguru Help Site - Print To PDF Using Microsoft's PDF/XPS Add-in

Introduction:
This article contains code that can be used to print worksheets or entire workbooks to a PDF file, using Microsoft's free Save as PDF or XPS add-in for Office 2007. These routines will NOT work in versions of Office prior to 2007. If you are running an earlier version of Office, and are running on Windows XP, then have a look at my PDF Creator articles.

Versions Tested:
These routines have been tested successfully using the following versions of the software:
Operating Systems:

Windows Vista Ultimate 64 bit edition. (Should work fine in 32 bit edition as well.)

Excel versions tested include:

Excel 2007

Code Samples
The first code sample will print the active sheet(s) to a single PDF file. (To select multiple sheets, hold down the CTRL key and click each sheet tab. Running this code will then print all selected sheets into a single PDF file.)

NOTE: In any of the routines below, to have Adobe Acrobat automatically open after printing to file, change the value of "OpenAfterPublish" to True. This is a handy way to review the output of the PDF, allowing you to make sure it was created correctly.

Code:

Sub SaveToPDF()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print active sheet(s) to PDF file using Microsoft's 2007 Add-in
    With ActiveSheet
        .ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:="C:\Test\Test.pdf", _
                OpenAfterPublish:=False
    End With
End Sub

The next sample uses code to specifically print two worksheets to a single PDF file.

Code:

Sub SaveSpecificToPDF()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print specific sheets to PDF file using Microsoft's 2007 Add-in
    Sheets(Array("Sheet1", "Sheet3")).Select
    With ActiveSheet
        .ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:="C:\Test\Test.pdf", _
                OpenAfterPublish:=False
    End With
End Sub

This code sample will save the entire workbook to a PDF.

Code:

Sub SaveFileToPDF()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print entire workbook to PDF file using Microsoft's 2007 Add-in
    ActiveWorkbook.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:="C:\Test\Test.pdf", _
            OpenAfterPublish:=False
End Sub

This sample will prompt the user for a location to save the active worksheet as a PDF.

Code:

Sub SaveFileToPDF_Prompt()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Save the file as a PDF to a location provided by the user at runtime
'               using Microsoft's 2007 Add-in
    Dim vPDFPath As Variant
    
    Do
        'Collect output file name and test if valid
        bRestart = False
        vPDFPath = Application.GetSaveAsFilename(, "PDF Files (*.pdf), *.pdf")

        'Check if user cancelled
        If CStr(vPDFPath) = "False" Then
            Exit Sub
        Else
            lAppSep = InStrRev(vPDFPath, Application.PathSeparator)
        End If

        'Check if file exists, as PDFCreator will not overwrite
        If UCase(Dir(vPDFPath)) = UCase(Right(vPDFPath, Len(vPDFPath) - lAppSep)) Then
            Select Case MsgBox("File already exists.  Would you like to overwrite it?", _
                               vbYesNoCancel, "Destination file exists!")
                Case vbYes
                    Kill vPDFPath
                Case vbNo
                    bRestart = True
                Case vbCancel
                    Exit Sub
            End Select
        End If
    Loop Until bRestart = False

    'Save the file as a PDF
    ActiveWorkbook.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=vPDFPath, _
            OpenAfterPublish:=False
End Sub

More Examples
For more examples on using Microsoft's PDF add-in, visit Ron deBruin's PDF page.