MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help!! - Automate PDF printing in VBA


Posted by JJ on August 21, 2001 11:11 AM

I am trying to print out an excel sheet to a PDF file. I would like to give the PDF file a name XXXXXX.pdf where XXXXXX is the value of cell A1 without touching the PDF file dialoge box. How can I do this is VBA?

I have tried
Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Acrobat PDFWriter on LPT1:"

and then use something like:

range("a1").select
pdfname = activecell.value
outputFilename = pdfname + ".PDF"

can someone help???

JJ


Posted by Ivan F Moala on August 21, 2001 11:42 PM

have a look @
http://www.pdfstore.com/mainpage.asp?WebPageID=227

Posted by JJ on August 22, 2001 6:21 AM

That helped a lot. Thanks! Here is the final code which I used if anyone is interested (the file path and file name are stored in A1 for example c:\XXXXXX.pdf - simple to do with a concatenate function). On closer inspection the SENDKEYS command is a really under-utilised function in VBA.....


Dim PDFFileName As String
Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
Range("A1").Select
Let PDFFileName = Application.ActiveCell
SendKeys PDFFileName & "{ENTER}", False
ActiveSheet.PrintOut
PSFileName = Chr(34) & PDFFileName & Chr(34)

: I am trying to print out an excel sheet to a PDF file. I would like to give the PDF file a name XXXXXX.pdf where XXXXXX is the value of cell A1 without touching the PDF file dialoge box. How can I do this is VBA? : Application.ActivePrinter = "Acrobat PDFWriter on LPT1:" : ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ : "Acrobat PDFWriter on LPT1:" : pdfname = activecell.value : outputFilename = pdfname + ".PDF"