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"
