Macro to Create PDF from excel with filename...

[Steve Hillin]

I'm using Excel to create invoices for my company. I want to then
save all the invoices to .pdf using Acrobat Distiller using the
invoice number as the file name. Can anyone help? I've pasted the
code I have below:

Sub PrintPDF()
'
'Keyboard Shortcut: Ctrl+p
'
'The invoice number is in cell I8. However, it is a function, and
therefore
'can't be pasted directly. So, I just copy it to J1, and work from
there.

Sheets("Invoice").Select
Range("I8").Select
Selection.Copy
Range("J1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

'This just puts the active cell back onto my document
Range("D8").Select

Dim strOldPrinter As String

strOldPrinter = Application.ActivePrinter

ActiveWindow.SelectedSheets.PrintOut Copies:=1,
ActivePrinter:="Actobat Distiller on LPT1:"

Application.SendKeys "{return}" & Range("J1") & "{return}", True
Application.Dialogs(xlDialogPrint).Show

Application.ActivePrinter = strOldPrinter

End Sub

When I run this macro, It starts printing to my Deskjet, and not the
Distiller.
If you can help me, please post here. Any help would be greatly
appreciated.

 

[Keith]

I would set the application.activeprinter to the
Distiller then print then change back (sort of what you
are doing.) Here are work we actually use the
activeprinter index to loop thru the printers until the
one we want is found then it prints. I would try that
method and see what the exact name of the printer is,
that could be the problem.

Keith
www.kjtfs.com

 

[Steve Hillin]

Can you or anyone provide some sample code that could loop through the
printers? I've thought of it not being named right, but how could I
find out? Thanks for any information.

 

[Keith R]

This is old (XL97 and maybe Acrobat 4) but should work with potentially
minor modifications; to get your current printer name just record a macro
while you print to the adobe, assuming distiller is still an option?:

Sub PrintPDF()

Application.ActivePrinter = _
"Acrobat Distiller on C:\Program Files\Adobe\Acrobat 4.0\PDF
Output\*.pdf"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Acrobat Distiller on C:\Program Files\Adobe\Acrobat 4.0\PDF
Output\*.pdf", _
Collate:=True

' ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
' "Acrobat PDFWriter on LPT1:", PrToFileName:="reser.pdf"
' Application.WindowState = xlMinimized

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
'give enough time for distiller to finish processing the file before
renaming it
Application.Wait waitTime

' Move and rename file.
OldName = "C:\Program Files\Adobe\Acrobat 4.0\PDF
Output\Myfilename.pdf"
NewName = "C:\Windows\desktop\PDFs\MyNEWfilename.pdf"
Name OldName As NewName
End Sub