• 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

     

    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!

    Comments 3 Comments
    1. garrymalone's Avatar
      garrymalone -
      Is is possible to specify "2 pages per sheet" as you can with a physical printer? The end result would be two (or more) worksheet charts on one PDF page.
    1. Ken Puls's Avatar
      Ken Puls -
      Hi Garry,

      I believe that the code will inherit the default page setup. So if you have your normal print jobs coming out that way, it should replicate it.
    1. therrm55's Avatar
      therrm55 -
      Hello Ken; I've been searching for a methodology to program a report output from excel into a PDF document and your code above works great. However, I have noticed 1 thing and then have a question on another as I'm stumped as to what to do for fixing one issue and figuring out how to print only the specific "pages" on a worksheet vs all the "pages" on a worksheet. Problem #1. After I ran you code and fixed the errors, my worksheet that I collect my data on is now locked and it seems I can't unlock it, yet there is no password protection on the worksheet, though it was protected. I go to design mode and my active x controls are not accessible, they've shrunk in size and moved to a location I would prefer they not be. I've not searched for a solution on that yet, but I thought I'd comment as perhaps you have seen this action before? The other part of my question is, in the PDF print/save process you provided, is there a way to only print the specific pages on a worksheet? I have an audit form that if I print the worksheet out to provide as report, I only want pages 1 thru 6 to print out. The other "6" pages are nothing but error checks to warn the user they've selected too many check boxes in a row or to allow the end user to know how many questions remain on their audit they are performing. I am trying to print just those 6 pages on the audit form and then a separate sheet that has a summary report. I've got the summary report to print along with the 12 Total pages on the audit worksheet. I'm struggling trying to figure out if I can reduce that to only the 7 pages I actually need. Hopefully you can point me in a direction I can go to get this resolved. Thanks in advance if you've got any suggestions. Best regards, Ted Herrmann
  • MVP Logo
  • Recent Forum Posts

    Bob Phillips

    VBA Programming help

    Code:
    Public Sub KeepHighest()
    Dim lastrow As Long
    Dim i As Long
        
        Application.ScreenUpdating = False
        With ActiveSheet
    ...

    Bob Phillips Today, 10:56 AM Go to last post
    Shoeboxken

    VBA Programming help

    I would like to use a VBA coding to create a list of information pulled from a data set.

    Data would include the following info as examples:...

    Shoeboxken Today, 05:36 AM Go to last post
    facams

    delete duplicate entries

    Thank you sir will try it...

    facams Yesterday, 09:00 PM Go to last post
    Bob Phillips

    delete duplicate entries

    Code:
    Public Sub DeleteDuplicates()
    Dim lastrow As Long
    Dim i As Long
        Application.ScreenUpdating = False
        
        With ActiveSheet
    ...

    Bob Phillips Yesterday, 08:25 PM Go to last post
    facams

    delete duplicate entries

    HI, new here. I need help in deleting duplicate cells that have same name or account # then based on an action that states "report" even if...

    facams Yesterday, 07:57 PM Go to last post