• Print To PDF Using Microsoft's PDF/XPS Add-in

    by
    Ken Puls
    Published on 2008-01-04 06:24 AM      Number of Views: 12693  
    0 Comments Comments
    Categories:
    1. PDF Creator - VBA Programming,
    2. Excel - VBA Programming
    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.

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

Gravatar by 1e2.it