VB script for converting each worksheet into individual pdf files.

[G]

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.

 

[Barb Reinhardt]

I assume you have the ability to Print to PDF? If you don't, a macro won't
do it.

 

[G]

I do have Acrobat 6.0 Professional installed. I am trying to run a macro
that would go to each worksheet in a workbook and create a pdf with the name
of the worksheet. I used the codes below but keep getting an error at:

Dim myPDF As PdfDistiller

Thanks again.

G


Sub testmePrint()

Dim wks As Worksheet
Dim PSFileName As String
Dim PDFFileName As String

For Each wks In ActiveWorkbook.Worksheets
With ActiveSheet
PSFileName = "C:\Documents and
Settings\C05354\Desktop\Testing\.ps" & .Name & ".ps"
PDFFileName = "C:\Documents and
Settings\C05354\Desktop\Testing\.pdf" & .Name & ".pdf"
.Parent.PrintOut copies:=1, preview:=False,
ActivePrinter:="Adobe PDF", PrintToFile:=True, collate:=True,
prtoFilename:=PSFileName

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""
End With

Next wks

End Sub

 

[Barb Reinhardt]

Maybe someone in the programming group can assist.

 

[Tom Ogilvy]

There is no built in support for it. If you have Adobe Acrobat (not Acrobat
Reader), then you can select a print driver which you can print to to
develop a pdf. There are 3rd party packages as well; I believe some are
Free. Try doing a google search on PDF and Excel.

 

[Jezebel]

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\Settings\PromptForPDFFileName
HKCU\Software\Adobe\Acrobat\PDFMaker\6.0\Global\Settings\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.


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.