This is surprisingly messy, and the answer is different for different
versions of Acrobat. I've seen these methods used, none of which is entirely
reliable --

Method 1 (works well, but is specific to Acrobat 6) --

1. Add to your project a reference to Acrobat PDFMaker.

2. Before printing, set these registry keys to zero:

HKCU\Software\Adobe\Acrobat\PDFMaker\6.0\Global\Se ttings\PromptForPDFFileName
HKCU\Software\Adobe\Acrobat\PDFMaker\6.0\Global\Se ttings\ViewPDFFile

(You might want to store the original settings so you can restore them
afterwards.)

3. Iterate the Sheets collection and print each by activating it and calling
AcrobatPDFMaker.ConvertToPDFA. This prints the active sheet, saving it under
the name of the workbook, in the same folder as the workbook. Rename this
file to the sheet name.

There's a gotcha with this method: Acrobat will save the workbook if
.Saved = FALSE, then re-open it. This kills any object references you
have to the book or any derivative object.


Method 2 (messier, but works with other versions of Acrobat, and quite a lot
faster than method 1) --

1. Manually set Acrobat as the default printer and uncheck the 'Prompt for
file name' and 'View output' options.

2. Iterate the sheets and print them using .PrintOut. This creates the PDF
in the default Acrobat folder, which you can find by looking at the 'port'
in Printers set-up. default is MyDocuments. Rename the output file. Note
that the PrintOut function runs asynchronously (ie your macro doesn't stop
and wait -- unlike with method 1), so you'll need a delay loop or use the
Sleep API function to wait until it's finished.







"G" wrote in message
...
I have a workbook with 80 worksheets. How do I create a macro that will
automatically convert each worksheets into pdf files?

Your help is greatly appreciated.