Automating PDF creation in Excel

I have just upgraded from Acrobat 5.0 to 6.0 and now the code that I created to automate the PDF creation does not work anymore.  I realize that now I must create a PostScript file first and then through Distiller Automation create the PDF files from the PostScript files.  Can anyone point me in the right direction on how to create the PostScript file in Excel using VBA?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Karl Heinz KremerCommented:
AFAIK Excel does not have a mechanism to print to file. You need to use the Sendkeys approach. Here is solution:
crussell96Author Commented:
I used the first solution, modified some of the code to fit my app but occasionally I get an error message stating: "Could not open the file: The specified path is invalid" when the program is trying to create the .ps files.  
Karl Heinz KremerCommented:
Is the path valid? Are you trying to overwrite an existing file? Does it work when you don't get this error message?
Is this error message a message box that your application displays, or are you just getting an error condition in your software?
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

crussell96Author Commented:
Its strange, I am trying to save it to a folder on my desktop.  At the point that I get the error message I see the 'Print to File' dialog box.  This box is supposed to contain the path to that folder on the desktop but sometimes it cuts off the first few letters of the path, for example the path listed in the dialog box is ':\Documents and Settings\crussell\Desktop\New Folder\', notice that it is cutting off the 'C'.  Sometimes it will cut off the 'C:\Do'.

This happens intermittently.  When the path does come out correct it creates the .ps files but will not create the .pdf files.  also, I am trying to overwrite the file if it already exists.

Karl Heinz KremerCommented:
This looks like the first few characters that you send with SendKeys are not making it to the right window. I'm not programming in VB, so I'm probably not the right person to ask for VB advice.

I would try to send a bunch of dummy characters that don't modify the path first, followed by the real path.

Once you have the PS file on the disk, you need to call Distiller. You can do this in two ways: Either use the automation API, or just call the command line version and specify the input and output file on the command line.
crussell96Author Commented:
Ok, I was able to fix the problem with the send keys not sending all the character.  I am trying to get to my finished product in steps.  Right now I can create the .ps file in a folder.  Right now I am opening up Distiller and selecting one of the files to convert to PDF but when I do that I get the following error message saying that i cannot create the PDF file:

Start Time: December 6, 2004 at 12:51 PM
Destination: C:\Documents and Settings\crussell\Desktop\New Folder\TimWkd1071.pdf
Adobe PDF Settings: C:\Documents and Settings\All Users\Documents\Adobe PDF 6.0\Settings\Standard(1).joboptions
%%[ Error: syntaxerror; OffendingCommand: ) ]%%
%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%
Distill Time: 0 seconds (00:00:00)
**** End of Job ****
Karl Heinz KremerCommented:
Does this happen with every file that you print? Would you be able to provide a sample PostScript file? If so, please email it to the email address from my profile page.
crussell96Author Commented:
Yes it is happening on all ps files created.  I sent you one of the ps files.
Karl Heinz KremerCommented:
This is not a PostScript file - here are the first few lines from the file:

%-12345X@PJL COMMENT DKAAP2DD Version for Windows 2000
@PJL COMMENT Dell Laser Printer 1700n
@PJL LJOBINFO USERID = "xxxxx" HOSTID = "xxxxx"
) HP-PCL XL;1;1;Comment Lexmark Emulation, NT 4.0 Driver

As you can see, this is a PCL file. You need to select a PostScript printer when you create your input file for Distiller. There are two ways you can do this: You can either use the already installed "Adobe PDF" printer, but you have to change it's configuration in the control panel so that it will always sent the fonts to the printer (file in this case), or you can install a second printer based on the PPD that's installed in C:\Program Files\Adobe\Acrobat 6.0\Distillr\Xtras. For this you need the Adobe PostScript driver (which you can download from Adobe's web site).
crussell96Author Commented:
Ok, so how do I specifically use the "Adobe PDF" printer?  Sorry, I am new to PDF file creation.

Karl Heinz KremerCommented:
Given that we have to deal with Excel, which does not provide an interface to do this, your best bet would be to define the "Adobe PDF" printer as your default printer. Otherwise you have to send keystrokes so that the correct printer gets selected in the list (which mans that you will get into trouble when you install a new printer).
crussell96Author Commented:
Is there any way to automatically create PDF files with VBA other than using SendKeys?  Unfortunately, its too unpredictable to use in this case.
Karl Heinz KremerCommented:
Not with Excel and Acrobat. The problem is that Excel does not allow you to select print to file, and this is necessary so that you can get a PDF file. You can use other programs that can craete PDF, but I'm not aware of anything that will behave just like PDFWriter did. If this is just one computer, you could leave Acrobat 5 installed (The A6 installer will ask you if it should remove the old version, but you can have both installed, and continue to use PDFWriter). Or, you could try something like PDF995 ( which claims to have a "Simple Programmers Interface". It's either free if you don't mind the web page that gets displayed, or $9.95. I don't have any experience with this tool (I've played with it a couple of years ago, but never used this programmign interface).
crussell96Author Commented:
I think I got mine to work.  I used 'PrToFileName:=PSFileName' and it creates the PS files then the PDF files.

Private Sub PrintIt(StartRow2 As String, PFolder As String)
Dim PSFileName As String, PDFFileName As String, DistillerCall As String
Dim ReturnValue As Variant
PSFileName = PFolder & "TimWkd" & Cells(StartRow2, 4).Value & ".ps"
PDFFileName = PFolder & "TimWkd" & Cells(StartRow2, 4).Value & ".pdf"

ActiveSheet.PageSetup.PrintArea = Range(Cells(StartRow2, 1), Cells(ActiveCell.Row, 15)).Address

'If the files already exist, delete them:
If Dir(PSFileName) <> "" Then Kill (PSFileName)
If Dir(PDFFileName) <> "" Then Kill (PDFFileName)

'Send path and filename using SendKeys

ActiveSheet.PrintOut , printtofile:=True, ActivePrinter:="Adobe PDF on Ne01:", PrToFileName:=PSFileName

'Convert PS file to PDF
Dim pdf As New ACRODISTXLib.PdfDistiller
pdf.FileToPDF PSFileName, PDFFileName, ""
Kill (PSFileName)
End Sub
PAQed with points refunded (75)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.