Print to PDF with Acrobat 7.0

[Richard Ward]

I have Adobe Acrobat 7.0 which is set as my default printer. I need to know
how to change the name of the print out after a macro is run changing the
data. What I have so far:

Application.DisplayAlerts = False
Application.Run "PERSONAL.xlsb!QuoteAge24"
Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11",
"12", "13", "14", "15", "16", "17", "18", "19", "20")).Select
Sheets("1").Activate

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Adobe
PDF on Ne04:", Collate:=True
Sheets("Pivot").Select


I have tried several ways of adding the file name/location for the output
with no success.

Any help would be appreciated.

 

[Al]

It would be very nice if we could program directly like this; however, with
venturing into the adobe api programming can become more difficult.

Here is possibly a better way. Acrobat comes with Distiller. Distiller has
the ability to "watch" a folder and when it finds a postscript file (*.ps) in
the watch file "In" folder, it will "automatically" convert it to a PDF and
move the results to the watch file "Out" folder.

To utilitze this functionality all you need do is print your Excel files to
a PostScript file in the distiller watch file "In" folder, and wait until it
is in the "Out" folder, then do with it what you like.

Do this to define a PostScrip Printer:
Define a new local printer named "PScript". Use Port "File: (Print to
File)". Find a PostScript printer to use as the Printer Model Number, any
PostScript Printe will do. I use "HP Deskjet 1200C/PS".

Do this to set up Distiller Watch files:
Start up Acrobat Distiller and define a "watch folder". Let's say you name
it "C:\ReportFiles". When you define it as a "watch folder", distiller will
add 2 subfolders named "In", and "Out". Also in Distiller you have the
option of "Deleting the postscript file", or "Moving it to the Out folder".
Make you choice.

Now you have a Windows printer that you can control through VBA code.

Here is basic VBA print to file code:
Application.ActivePrinter = "PScript on FILE:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PScript
on FILE:", PrintToFile:=True, Collate:=True,
PrToFileName:="C:\ReportFiles\In\PrintFile.ps"

To open the PDF file use this code:
Application.FollowHyperlink C:\ReportFiles\In\PrintFile.ps

To launch Acrobat Distiller programmatically use this code:
shell C:\Program files/Adobe/Acrobat 7.0\Distillr\acrodist.exe"

Let me know how it works!!

 

[Richard Ward]

I followed the intructions provided, the macro runs, but there is no output
file in the in, or out folder. Here is what I have:

Application.DisplayAlerts = False
Application.Run "PERSONAL.xlsb!QuoteAge24"
Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11",
"12", "13", "14", "15", "16", "17", "18", "19", "20")).Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1,
ActivePrinter:="PScript", PrintToFile:=True, Collate:=True,
PrToFileName:="C:\ReportFiles\In\TestPrintFile.ps"


Sheets("Pivot").Select

 

[Al]

Your code looks good. I copied it and tested it. It worked fine. These are
the few changes I made:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PScript",
PrintToFile:=True, Collate:=True,
PrToFileName:="C:\ReportFiles\in\TestPrintFile.ps"

Are you sure your code is getting to this statement? You might try stepping
through the code to verify.

 

[Richard Ward]

I got all the way through the code, I even got prompted for a file name (I
didn't expect this) and there was nothing in the "in" or "out" folders.

 

[Al]

You should not be prompted for a file. They command supplies the file name.
You have something wrong in the syntax.

What I found was that this entire command could not be continued to multiple
lines and work properly. My guess is that if your command is on multiple
lines and your last line is the file name it's not reading that line as part
of the command. Try bunching it together on 1 line.

Did you compile the code (Debug, compile)? Was compile successful?