VB routine to print to PDF?

[fedude]

I'm trying to write a routine that will print a pdf page. I need to make
this a routine because I have to loop through a large set of data and print a
pdf page for each row of the data.

My typical method for writing a routine is to record a macro and then modify
the module I created. Unfortunately, when I print to pdf using acrobat
distiller, the macro does not record all the steps. In particular is does
not collect the filename or location. Here is the macro I created when I
recorded my keystrokes:

Sub pdfPrint()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

I need to specify the filename and location in the macro (different for each
row).
Maybe I should be using another tool for creating the pdf files? Any help
would be appreciated.

Ecxel 2003/Acrobat Distiller 5

 

[Pete_UK]

Here's a snippet of code that I put together a few years ago to do a
similar thing:

my_file = ActiveCell.Value
If my_file <> "" _
Then
'as long as it is not blank, open the file, change
'to the Adobe printer, and print the pdf file
ChDir (this_client)
Workbooks.Open Filename:=my_file
my_printer = Application.ActivePrinter
Application.ActivePrinter = "Adobe PDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
ActivePrinter:="Adobe PDF on Ne02:", Collate:=True
'switch back to installed printer driver
Application.ActivePrinter = my_printer
ActiveWorkbook.Close
ChDir ("..")
Else
'if the filename is blank, then don't check any more
j = num_users
End If

Excel files were stored in sub-directories under the name of the
client, and were written to a sheet in the master file such that each
filename for a particular client was in the same column. A loop cycled
through each client and then through each filename from the sheet, so
my_file was opened in sequence and a .pdf file of the same name was
written to the same sub-folder and then my_file was closed in
readiness for the next one. You might need to change Ne02 (twice) to
Ne01 to suit your requirements.

This was using Adobe Acrobat v7, but you might be able to adapt it to
suit your circumstances.

Hope this helps.

Pete

 

[fedude]

Pete,

I don't see where you set the pdf filename? When I tried a similar code
snippet, the adobe distiller opened up a "save as filename" dialog. I can
probably suppress the dialog in distiller somewhere, but I still need to
assign the pdf filename via VB.

Still perplexed.......

 

[Pete_UK]

I haven't used it for a couple of years, but I think there are several
options that you can set in the Adobe printer driver, one of which is
to set the filename the same as the Excel filename - that's the way I
set it anyway, as the filename had been built up from individual names
and a datestamp for that month.

Pete

 

[Pete_UK]

I still have the Adobe printer driver installed, so have just checked
- click on Preferences in the driver and you have several choices, one
of which is "Adobe PDF Output Folder". Here you can choose between
"Prompt for PDF filename" or "My Documents\*.pdf" so you set it up
with the second of these if you don't want to be asked for a filename
everytime around the loop.

Hope this helps.

Pete