Automating printing to PDF

[Steve Stewart]

Hi all

I'm trying to automate a print-to-PDF process in Excel 2002. I'm
using a PDF converter that comes as part of an excel add-in called
Vision XL.

This is what I've got so far....

Sub print_PDF()
Application.ActivePrinter = "Vision PDF on LPT1:"
Worksheets(Array("MRA2", "HMA")).Select
ActiveWindow.SelectedSheets.PrintOut , , , , , printtofile = True _
, , prtofilename = "c:\test.pdf"

End Sub

Running the macro produces a 1004 error and whilst the print process
sppears to be activated it doesn't create the file in the path shown
above. I know others have posted similar problems but I've still to
find a solution that works.

BTW, writing VBA is not my normal 9 - 5 job so please keep any
suggestions simple

TIA,

Steve

 

[Myrna Larson]

If that's your actual code, one of the arguments is misspelled. It's PrintToFile (two T's), not
printofile. With named arguments, you use a colon and equal sign =) not an equal sign, and
you don't need to supply commas for missing arguments.

That said, the following syntactically correct statement did not work after I set the default
printer to the "Adobe PDF" printer (I have Acrobate 6, and the printer name is different).

ActiveWindow.SelectedSheets.PrintOut PrintToFile:=True, prtofilename:="c:\test.pdf"

 

[Dave Peterson]

The original post had two t's in printtofile (but was missing the : in :=).

But without the colon, I got a syntax error--not 1004.

Just curious (I don't own adobe's writer): If you do the same thing manually,
does it work?

At work, both(!) times I've created a .pdf, I've printed to a file associated
with a postscript printer (and used the distiller).

 

[Myrna Larson]

When I print to the Adobe PDF "printer" manually, I get a "Save PDF File As" dialog box where I
have to enter the file name.

The following link will take you to an article on the Adobe web site that describes how to turn
off the prompt for a file name when you are using the Distiller printer.

http://www.adobe.com/support/techdocs/2800a.htm

If you change the printer settings to not ask for a file name, it uses the same name as the
source document, but with a PDF extension. I believe they recommend putting these files all in a
separate directory set up for this purpose.

In Acrobat 6, I see similar settings when looking at the properties for the Adobe PDF printer,
but I haven't tried it.

 

[Dave Peterson]

Thanks for the link.

At work, we have a separate share that has a chron job running every 5 (about)
minutes that convert the .ps to .pdf. (So it's even simpler for me <bg>.)