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:

• The Save As PDF and XPS add-in
• The Save As PDF add-in
• The Save As XPS add-in

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.