Office PDF/XPS Addin

One of the big things that Office 2007 was supposed to bring was the ability to save to a PDF file, without getting extra software. Somewhere along the line, they decided to make this feature an Add-in, which can be downloaded from Microsoft. Funny enough, they have three different ways you can download it:

For my sake, I downloaded the PDF and XPS add-in to try the PDF features in Office 2007. (It won't work in 2003.) As an aside, I have never seen an XPS file in the wild, so may revisit that format when I need to.

A few of you may wonder why I bothered with this at all, since I have a few pages dedicated to PDFCreator on my main site. The answer, sadly, is Vista. PDFCreator works great on Windows XP, (I have some revised articles to post about it, actually,) but it doesn't work on Vista. They are actively fighting the issue, but so far it's eluded the development team. I really hope they nail it, as I was less than impressed by the half baked PDF add-in that Microsoft has supplied.  (Update... the Vista issue has been fixed.)

Okay, maybe I'm being a bit unfair by calling it half baked. Here's the benefits of the Microsoft Add-in:

  • It's free
  • I will allow a user to save to a PDF file easily
  • It is found under "Save As", rather than being treated as a printer, which could be less confusing to new PDF users
  • You can code a routine to use the PDF add-in

Honestly, the biggest thing that made me dig into PDFCreator in the first place was that it was the only PDF writer which exposed it's object model for free. I'm rather cheap, so I didn't want to pay for the license to do so with CutePDF, Adobe, or any other engine. Had I come upon the Microsoft version at that time, I probably would have been quite satisfied.

So what's missing?

For starters, security. I find this pretty ironic, considering the huge push that Microsoft has been on to create secure environments everywhere. The PDF/XPS add-in does not allow you any method of setting security on your PDF documents. Maybe it's not important to most users, but it should be an available option, IMO.

The logic behind using this add-in is also different. The logic for printing multiple sheets to a single PDF would be as follows:

PDFCreator:

  • Loop through each sheet checking if we want to print it
  • "Print" the job to temporary queue
  • Combine all the jobs in the temporary queue into a single job
  • Print the job to the PDF file

Using the MS PDF Add-in:

  • Loop through each sheet checking if we want to print it
  • Select all the sheets we want to print
  • Export all the active sheets to the PDF file

Some sample code of printing multiple specific sheets to a PDF using the MS PDF Add-in:

[vb]Sheets(Array("Sheet1", "Sheet3")).Select
With ActiveSheet
.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:\Test\Test.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End With[/vb]

So here's the question... why do we have to select the array of sheets first? I hate selecting anything, as it's slow and, IMO, unreliable. I also despise dealing with ActiveSheet, as I never trust my users to take their hands off the keyboard when the macro is running.

Now, you can print a single sheet by referring to it directly, but not multiple sheets unless you print the entire workbook. This is where PDFCreator is superior. With PDFCreator, I can loop through the sheets, referring to them directly, printing the ones I want, and combine them into one job.

Another really big thing... while I have not done it yet, I can see no reason why you wouldn't be able to print reports from multiple applications to a single PDF using PDFCreator. That could absolutely not be done with the MS version, as you're stuck with the current application by nature of the "Activesheet" issue. Come to think of it, maybe I should write a routine to demonstrate the process using PDFCreator. Being able to print Word, Excel & Powerpoint documents to a single PDF could make for some really nice reports...

The final thing that I can say in PDFCreator's favour over the MS add-in is that PDFCreator can be used manually from any program, as it's just a printer. Not so with the MS add-in which is specific to the Office suite.

In Microsoft's defense, I will have to admit that there is much less code involved using their Add-in. A large part of this is due to the fact that you don't need to bind to the PDFCreator class to get going, clean up, etc...

At any rate, I'd be remiss in not showing a couple of other MS PDF examples as well before I end the post, so here goes:Here's an example of printing the active sheet to PDF, which does not automatically open the completed PDF.

[vb]With ActiveSheet
.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:\Test\Test.pdf", _
OpenAfterPublish:=False
End With[/vb]

And here's an example of printing the entire workbook to a PDF file. This one DOES open the completed PDF in Acrobat once it is complete.

[vb]ActiveWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:\Test\Test.pdf", _
OpenAfterPublish:=True[/vb]

Naturally, you can also have it print to a variable sheet location such as:

[vb]ActiveWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Worksheets("FilePath").Range("A1").Value, _
OpenAfterPublish:=True[/vb]

I'll admit that despite the fact that the code for PDFCreator appears more complicated, I've got a lot of time invested in it, and know it fairly well. That may make me a little biased, but I really enjoy the robustness of what PDFCreator has to offer. I feel that it gives me more control over things, which is very important to me. I just wish that the team could get their Vista issue sorted.

14 thoughts on “Office PDF/XPS Addin

  1. Of course, after posting this, and making a page on my site for it, I discovered that Ron deBruin covered this topic last April! (Even links back to my site for earlier versions of Excel)

    For more examples of using Microsoft's PDF Add-in for Office 2007, including how to email PDF's, check out Ron's PDF page.

    🙂

  2. Ken -

    The first thing I'd try is to replace this:

    Sheets(Array("Sheet1", "Sheet3")).Select
    With ActiveSheet

    with this:

    With Sheets(Array("Sheet1", "Sheet3"))

    I never downloaded the PDF/XPS add-in because, like you, I'm happy with PDFCreator, and the only XPS files I've ever seen have come out of Redmond.

  3. Hi Jon,

    Yep, tried that. 😉

    For whatever reason, trying to use the ExportAsFixedFormat method on the sheets collection generates a run time error '438'. (Object doesn't support the property or method.)

  4. Hi Ken,

    I'm a little late to the PDFCreartor party it seems. But having discovered your posts on automating it earlier today, I went ahead and installed it.

    My previous PDF coding has been successful with Adobe's Distiller, but I really want to use a free product if at all possible.

    The first attempts with based on your examples went well, but suddenly the PDF Creator object hangs on the cClose method, and just won't stop.

    I had a look around the pdfforge site, and found a similar problem described, but couldn't find any proposed solutions. Googling the web and the groups didn't yield anything useful.

    Have you run into this one?

    I'm using Excel 2003 on an XP Home SP2 box.

    I really appreciate any help, or even hints, that you might be able to provide. TIA.

  5. Just for the record, I discussed this offline with Sandy, and provided a revised method based on the methodology here. The issue persisted until Sandy removed the cClose method from the routine.

  6. Pingback: Meerdere bladen naar een PDF file exporteren in 2007 - Worksheet.nl

  7. Hi Ken,

    Your macro has literally saved me hours, if not days of tedious work.

    Thanks so much.

  8. Hi ken,

    Really great post 🙂
    I'm trying to automat printing to PDF from a google search. Is there a way of printing lets say 10 pages and then combine theme to 1 page in a choosen folder?
    I have been googeling so much and did not find nothing at all that could help.

    Greetings.

  9. Hi Ken,
    I mean web pages.
    Is it possible to merge pdf files with the microsoft solution?
    If I have some PDF files already in folder, selectem all and the merge them and give it a pre arranged name?

    Greetings.

  10. Hi Ken,

    One more thing, I can only use CutePDF writer to print to PDF, but I can use the save to PDF under Google Chrome, not under Internet Explorer 8 (what in my opinion is sily).
    Any solution to print to PDF from a google search would be very welcome.

    Greetings.

  11. I'm not familar with CutePDF. If you can install PDFCreator though (it is free), you could do this. You print to PDFCreator, but set the printer to hold all jobs. Then you could combine them and print them once combined.

  12. Hi Ken,

    After finding out some of our team members have actually a copy of Adobe Acrobat Standard 8 installed, this will make things easier. I made in the past a macro that merges all pdf files in a folder. I just need to combine the solutions into one, some how and when the first parts finish, merge the pdf files.

    Greetings.

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *