Automated Print from Excel to PDF

[Noel]

Each month, I create many Excel spreadsheets that I manually save as PDFs for
distribution to my team. I'd like to automate this process using a macro.
I've seen the following code online and have attempted to use it, but receive
an error in the Dim MyPDF line of code indicating that the user-defined type
is not defined.

I'm using Excel 2003 and Acrobat Distiller 8. I have no problem creating
PDFs manually

THANKS!

Sub create_pdf()

' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "c:\myPostScript.ps"
PDFFileName = "c:\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet

MySheet.Range("A1267").PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, _
prtofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller '<==== ERROR HERE: User-defined type not defined
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

 

[JP]

Try going to Tools > References and checking if you can find an entry
for Acrobat Distiller?

--JP

 

[Noel]

OMG -- I can't believe I missed that. I selected Distiller and I no longer
receive the error --THANKS! However, the code does not produce a file. It
goes through the motions and I receive a pop-up saying that it's printing the
two pages of the document, but no PDF is actually produced.

The PS file gives the following:

%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%
Distill Time: 00 Hour(s) : 00 Minute(s) : 00.62 Second(s)
**** End of Job ****

The PDF log gives the following:

%%[ Error: undefined; OffendingCommand: E *r0F &u600D ]%%
%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%