• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

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?

Thanks
0
crussell96
Asked:
crussell96
  • 7
  • 7
1 Solution
 
Karl Heinz KremerCommented:
AFAIK Excel does not have a mechanism to print to file. You need to use the Sendkeys approach. Here is solution: http://www.planetpdf.com/mainpage.asp?webpageid=762
0
 
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.  
0
 
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?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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\TimWkd0174.ps', 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.

Thanks
0
 
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.
0
 
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
Source: TimWkd1071.ps
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 ****
0
 
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.
0
 
crussell96Author Commented:
Yes it is happening on all ps files created.  I sent you one of the ps files.
0
 
Karl Heinz KremerCommented:
This is not a PostScript file - here are the first few lines from the file:

%-12345X@PJL COMMENT DKAAP2DD Version 9.3.0.0 for Windows 2000
@PJL COMMENT Dell Laser Printer 1700n
@PJL SET PAGEPROTECT = AUTO
@PJL SET LIMAGEENHANCE = ON
@PJL SET LIMAGEENHANCETYPE = 2
@PJL SET LPARM : PCL LCOLOREXTENSIONS = ON
@PJL SET LCOLLATION = ON
@PJL SET LMULTIPAGEPRINT = OFF
@PJL SET LPARM : PCL LFONTCOMPATIBILITY = PCL5
@PJL LJOBINFO USERID = "xxxxx" HOSTID = "xxxxx"
@PJL SET RESOLUTION = 600
@PJL SET TIMEOUT=0
@PJL ENTER LANGUAGE=PCLXL
) 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).
0
 
crussell96Author Commented:
Ok, so how do I specifically use the "Adobe PDF" printer?  Sorry, I am new to PDF file creation.

Thanks
0
 
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).
0
 
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.
0
 
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 (http://pdf995.com/) 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).
0
 
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
frmTimeWkdPdf.Repaint
DoEvents

'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
0
 
moduloCommented:
PAQed with points refunded (75)

modulo
Community Support Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now