Print to PDF using Adobe Acrobat 6.0

[JeffTO]

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
it
wont print anything - in the print cue it says it is trying to print
to Port 'MyDocuments\*.pdf;


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


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


Thanks,


Jeff

 

[jaf]

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

John

 

[JeffTO]

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

Thanks,

Jeff