On 20 Feb 2006 12:38:29 -0800, wrote in microsoft.public.excel:

>Thank you for the response. However, I already know and have the
>capability of saving the worksheet as a PDF, but what I want to do is
>record it in a macro so that it will automatically Print/Save and
>insert the value in j19 as the filename. I do not want to have to
>enter in the information manually every time.

These things can not be recorded in macros, you have to cut the
code manually.

Adobe Acrobat offers a COM interface to the VBA programmer; it's pretty
expensive. Alternatively, the latest version of PDFCreator now also
provides a COM interface and the distribution file includes examples.

Here is a skeleton (taken from actual working code, but not tested
in this incarnation):

Option Explicit
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

Sub PrtPDFCreator(theObject As Object)
' Print to the PDFCreator printer
' Needs a reference to PDFCreator.exe

Const theFileRoot As String = "C:\Temp\PDFs\" ' Testing (must exist)
Dim outName As String
Dim strOldPrinter As String

' Add a reference to PDFCreator
Dim myPDFCreator As PDFCreator.clsPDFCreator

outName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "-" _
& Application.ActiveSheet.Name & "-" _
& Format((Now), "HHNN-SS") & Format((Timer() * 100) Mod 100, "00") & ".PDF"

Set myPDFCreator = New PDFCreator.clsPDFCreator
With myPDFCreator
If .cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = theFileRoot
.cOption("AutosaveFilename") = outName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

' Perform the print action
strOldPrinter = ActivePrinter
theObject.PrintOut ActivePrinter:="PDFCreator"

' Wait until the print job has entered the queue
Do Until myPDFCreator.cCountOfPrintjobs = 1
DoEvents
Sleep 100
Loop
Sleep 100
myPDFCreator.cPrinterStop = False

' Wait until the PDF file shows up
Do Until Dir(theFileRoot & outName) <> ""
DoEvents
Loop

myPDFCreator.cClose
Set myPDFCreator = Nothing
Sleep 100
DoEvents
ActivePrinter = strOldPrinter
End Sub

Private Sub myPDFCreator_eError()
MsgBox "ERROR [" & myPDFCreator.cErrorDetail("Number") & "]: " _
& myPDFCreator.cErrorDetail("Description"), vbCritical + vbOKOnly, "PrtPDFCreator"
End Sub

Sub testPDFCreator()
Dim mySheet As Worksheet

For Each mySheet In ActiveWorkbook.Sheets
mySheet.Activate
Call PrtPDFCreator(mySheet)
Next mySheet
MsgBox "Done."
End Sub

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"