Print to PDF using Adobe Acrobat 6.0



Hi All

I am trying to automate the printing of PDF files from Excel - I have
found a lot of posts on this topic and none seem to answer my problems
on how to do this and I am running into a bunch of problems - here is
what I am usnig and I what I have so far:

Excel 2003
Adobe Reader and Distiller 6.0

I understand that you have to create a postscript file first and then
using distiller create the pdf file

I am having problems doing both:

HEre is the code I am using (modified from another post I found)

' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String

PSFileName = "C:\Test.PS"
PDFFileName = "C:\Test.PDF"

'Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet
MySheet.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe
PDF", printtofile:=True, collate:=True, prtofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

When I try to create the ps file I get an error in my Print Cue and
wont print anything - in the print cue it says it is trying to print
to Port 'MyDocuments\*.pdf;

Is 'Adobe PDF' the Adobe PostScript print drive I am supposed to be
Is there something in my Adobe settings that might be causing the

Next I have a PostScript file (taken from an Adobe Sample) that I was
trying to use the last half of my code to use the distiller to create
a PDF - here I am getting this error:

Run-Time Error '429'
ActiveX component can't create object

I have a reference to Adobe Distiller in my VBA project so I am not
sure what is causing this error.

So in a nutshell my problem is twofold - I cant create a postscript
file and I cant create a PDF from a PostScript file

Any assistance would be greatly appreciated - if you need any more
details please let me know




Hi Jeff,
The "Adobe PDF" refers to the "PDFWriter" not distiller. The .ps file extension should trigger distiller.

MySheet.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, collate:=True, prtofilename:=PSFileName
Should be...
MySheet.PrintOut copies:=1, preview:=False, ActivePrinter:=False, printtofile:=True, collate:=True, prtofilename:=PSFileName



Hi Jon

Thanks for the reply

It is still not working - I am still getting the same error when
trying to create the PostScript file.

When I change the ActivePrinter:=False it picks up whatever the last
printer used was and if it was Adobe PDF I get the same error as
before as it is tring to create the PDF file - if it was a network
printer I get a Print Error as it is attempting to print to the
network printer.

It is not attempting to use distiller at all

Any other thoughts or suggestions is very much appreciated



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question