Convert PS File to PDF

[AlexMach]

Every day I create many Excel reports that I manually save as PDFs for
distribution to my stakeholders. 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

[COLOR=#273d49][FONT=Arial]Sub Create_PDF()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]Dim tempPDFFileName As String<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]Dim tempPSFileName As String<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]Dim tempPDFRawFileName As String<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]Dim tempLogFileName As String<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]Dim x As Integer<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]Dim ws As Worksheet<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]Dim PDFPath As String<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]ThisWorkbook.Worksheets(3).Select<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]For x = 3 To ThisWorkbook.Worksheets.Count<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]        If Worksheets(x).Visible = xlSheetVisible Then Worksheets(x).Select (False)<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]Next x<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]PDFPath = ActiveWorkbook.Path & Application.PathSeparator<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]tempPDFRawFileName = PDFPath & Left(Format(Date, "mm.dd.yyyy") & " " & wsAgenda.Range("I2"), Len(Format(Date, "mm.dd.yyyy") & " " & wsAgenda.Range("I2")))<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]tempPSFileName = tempPDFRawFileName & ".ps"<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]tempPDFFileName = tempPDFRawFileName & ".pdf"<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]tempLogFileName = tempPDFRawFileName & ".log"<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, Collate:=True, prtofilename:=tempPSFileName<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]Dim myPDF As PdfDistiller '<==== ERROR HERE: User-defined type not defined
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""<o:p></o:p>[/FONT][/COLOR]
[COLOR=#273d49][FONT=Arial]<o:p> </o:p>[/FONT][/COLOR]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]Kill tempPSFileName[COLOR=#273d49][FONT=Arial]<o:p></o:p>[/FONT][/COLOR][/FONT][/COLOR][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]Kill tempLogFileName[/COLOR][/SIZE][/FONT]
<o:p>[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000]End Sud[/COLOR][/SIZE][/FONT]

<o></o>

 

[Diffy]

Maybe define the library?

Dim myPDF As ACRODISTXLib.PdfDistiller
Set myPDF = New ACRODISTXLib.PdfDistiller

 

[AlexMach]

Thanks Diffy, I already tried that and it came back with the same error message