A little while ago I needed to automate printing PDF files from Excel giving each PDF a filename which was sourced from cell A1. The final code which I used if anyone is interested was as follows (the file path and file name are stored in cell A1 for example c:XXXXXX.pdf where Cell A1={XXXXXX} simple to do with a concatenate function).
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)
However, when you print a sheet with embedded charts from Excel via PDF Writer in the Print menu (as above) the final PDF is far from perfect (lines move strangely around and bar labels are offset etc.)
A far better and more accurate solution is to actually click the ICON PDF Maker 5 for Excel in the Menu bar and go through the distillation process in full (in this case the PDF Maker states the file should be saved before it can continue etc.).
Is there a way to undertake the last ‘ICON approach’ through VBA, again, selecting Cell A1 as the PDF name?? I tried to record a macro and go through the ‘ICON approach’ but all the VB editor shows after the process is blank…..
I’ve had a look at http://www.pdfstore.com/mainpage.asp?WebPageID=227 and can’t find any good help.
Any comments would be gratefully appreciated!!
JJ
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)
However, when you print a sheet with embedded charts from Excel via PDF Writer in the Print menu (as above) the final PDF is far from perfect (lines move strangely around and bar labels are offset etc.)
A far better and more accurate solution is to actually click the ICON PDF Maker 5 for Excel in the Menu bar and go through the distillation process in full (in this case the PDF Maker states the file should be saved before it can continue etc.).
Is there a way to undertake the last ‘ICON approach’ through VBA, again, selecting Cell A1 as the PDF name?? I tried to record a macro and go through the ‘ICON approach’ but all the VB editor shows after the process is blank…..
I’ve had a look at http://www.pdfstore.com/mainpage.asp?WebPageID=227 and can’t find any good help.
Any comments would be gratefully appreciated!!
JJ
