Automating printing to PDF
 

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

Automating printing to PDF
 

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"


On 14 Jul 2003 11:00:01 -0700, (Steve Stewart) wrote:

The printer is definitely not misspelt, in fact when the macro is run
the print job icon appears in the bottom right of the taskbar as you'd
expect.

The line of code that is causing the problem is:

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

I don't think the PDF converter is being used properly. Somebody has
suggested that I cannot use 'prtofilename' to get the desired effect
but I don't know of any other way of achieving the same result as my
macro 'should' do.

If anyone can help it would be much appreciated :)

TIA,

Steve S



Dave Peterson wrote in message ...
I don't have a pdf printer, but I could get a 1004 error by misspelling the name
of the printer. Did you type it or did you steal the code generated when you
did recorded a macro?

Any chance it's that simple?

If not, what line of code caused the error?

Automating printing to PDF
 

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 wrote:

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"

On 14 Jul 2003 11:00:01 -0700, (Steve Stewart) wrote:

The printer is definitely not misspelt, in fact when the macro is run
the print job icon appears in the bottom right of the taskbar as you'd
expect.

The line of code that is causing the problem is:

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

I don't think the PDF converter is being used properly. Somebody has
suggested that I cannot use 'prtofilename' to get the desired effect
but I don't know of any other way of achieving the same result as my
macro 'should' do.

If anyone can help it would be much appreciated :)

TIA,

Steve S



Dave Peterson wrote in message ...
I don't have a pdf printer, but I could get a 1004 error by misspelling the name
of the printer. Did you type it or did you steal the code generated when you
did recorded a macro?

Any chance it's that simple?

If not, what line of code caused the error?



--

Dave Peterson

Automating printing to PDF
 

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.

On Mon, 14 Jul 2003 18:51:32 -0500, Dave Peterson wrote:

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 wrote:

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"

On 14 Jul 2003 11:00:01 -0700, (Steve Stewart) wrote:

The printer is definitely not misspelt, in fact when the macro is run
the print job icon appears in the bottom right of the taskbar as you'd
expect.

The line of code that is causing the problem is:

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

I don't think the PDF converter is being used properly. Somebody has
suggested that I cannot use 'prtofilename' to get the desired effect
but I don't know of any other way of achieving the same result as my
macro 'should' do.

If anyone can help it would be much appreciated :)

TIA,

Steve S



Dave Peterson wrote in message ...
I don't have a pdf printer, but I could get a 1004 error by misspelling the name
of the printer. Did you type it or did you steal the code generated when you
did recorded a macro?

Any chance it's that simple?

If not, what line of code caused the error?

Automating printing to PDF
 

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



Myrna Larson wrote:

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.

On Mon, 14 Jul 2003 18:51:32 -0500, Dave Peterson wrote:

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 wrote:

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"

On 14 Jul 2003 11:00:01 -0700, (Steve Stewart) wrote:

The printer is definitely not misspelt, in fact when the macro is run
the print job icon appears in the bottom right of the taskbar as you'd
expect.

The line of code that is causing the problem is:

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

I don't think the PDF converter is being used properly. Somebody has
suggested that I cannot use 'prtofilename' to get the desired effect
but I don't know of any other way of achieving the same result as my
macro 'should' do.

If anyone can help it would be much appreciated :)

TIA,

Steve S



Dave Peterson wrote in message ...
I don't have a pdf printer, but I could get a 1004 error by misspelling the name
of the printer. Did you type it or did you steal the code generated when you
did recorded a macro?

Any chance it's that simple?

If not, what line of code caused the error?



--

Dave Peterson