Print To pdf Not Working

[jutlaux]

I am trying to print a excel worksheet to a pdf file. Here is the code:

Sub SaveAsPDF ()

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", PrintToFile:=True, Collate:=True,
PrToFileName:="C:\WeeklyDTReport.pdf"

End Sub

Everything seems to be working correctly due to the fact that I do get the
..pdf file at the correct location with the correct name. The problem I have
is when I go to open the file using Adobe Reader I get the following error:

"Adobe Reader could not open 'WeeklyDTReport.pdf' because it is either not a
supported file type or because the file has been damaged (for example, it was
sent as an email attachment and wasn't correctly decoded)."

If I set PrintToFile = False and browse to the path and enter the name when
prompted Adobe opens the file fine.

What am I missing?

Thanks

 

[Barb Reinhardt]

I've seen some saveas code differences between 2003 and 2007 that IIRC have
the same problem. What version of Excel are you using?

 

[jutlaux]

2003

I've seen some saveas code differences between 2003 and 2007 that IIRC have
the same problem. What version of Excel are you using?

 

[Barb Reinhardt]

You may find something here that's helpful.

http://www.rondebruin.nl/pdf.htm

 

[jutlaux]

Unfortunately I am not using 2007 so the add-in and code will not work.

 

[SM]

I had a similar issue and we use a product called freeware GhostScript with
word 2003. Ghostscript is installed as a PDF printer driver. You can do a
..PrintOut to this printer to a file that will generate a .PS (postscript)
file. You can then call ps2pdf.bat file supplied by GhostScript from within
word to convert the .PS file to .PDF!!

Hope this helps.
SM.

 

[nthnprmn]

If you are using the "Adobe PDF" print driver that is installed with Adobe Acrobat Pro with Excel VBA code to generate PDFs, there are two things I found that must be done:

1) Turn OFF the option "Do not send fonts to Distiller"; from what I've found by doing this the PrToFileName parameter in the VBA code in combination with this print driver option results in PostScript fonts being hydrated into the file that is generated - it becomes a PostScript file; I ended up renaming my file extensions in code from PDF to PS to avoid confusion.

2) Open Adobe Acrobat Pro and use it to convert the .ps files generated from the VBA code into a PDF binder file, or individual PDF files. This echoes what SM was suggesting with running the ps2pdf.bat against the .ps files to create PDFs.

This seems like a hack, but in my case the user of the solution I'm developing would have had to combine the individual PDFs into a PDF binder file anyway. Instead they are combining individual PS files into one PDF binder file - so no real extra work on their part.

As a point of reference, I'm developing my VBA solution in Excel 2003.

I hope this post saves someone else hours of frustration.

Cheers!