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

On Mar 15, 12:29*am, fedude wrote:
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" wrote:
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

On Mar 14, 1:28 am, fedude wrote:
* 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- Hide quoted text -

- Show quoted text -