• 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.

     

    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

    needexcelhelp

    Convert large dataset into 2 columns into multiple columns

    Hi. I have a large dataset from multiple experiments that are all in two columns, when in reality they should be split into 60 or so columns from 30 separate...

    needexcelhelp Yesterday, 11:20 PM Go to last post
    Hercules1946

    week num

    Excels weeknum function can result in a week 54 in some years! This is because:
    1. 1st January is always Week 1.
    2. But .... there is also...

    Hercules1946 Yesterday, 11:16 PM Go to last post
    myk2thestar23

    Highlighting Cells based on Date

    I need help trying to accomplish this to save me some manual work.

    I have an excel spreadsheet where I need to Highlight cells in Column...

    myk2thestar23 Yesterday, 11:10 PM Go to last post
    jonespandrew

    WEEKnUM AND PIVOT TABLES

    Hi Thanks

    However the forumula used in previous post is showing 31/12/17 as week 53 when it should be 52 how do i alter the formula to correct...

    jonespandrew Yesterday, 07:00 PM Go to last post
    jonespandrew

    week num

    Hi thanks for this, the only issue now is that it is shows 31/12/17 as week 53 when infact it is week 52, what do i need to do to alter this....

    jonespandrew Yesterday, 06:59 PM Go to last post