Trouble using VBA and PDF Distiller to PDF Embedded Excel Charts

[taduh]

Hi!

I am attempting to use VBA code and the FileToPdf syntax to print an embedded Excel chart to a PDF file. The code I use creates a PostScript file first, then attempts to use the FileToPDF syntax to create the PDF, however the PostScript file gets created, but the FileToPDF line will not create the PDF file.

I can double-click the postscript file and it will invoke the distiller to create the PDF, but I need to have the PDF file created in an automated fashion.

I have no trouble creating PDFs for spreadsheets using this same methodology. Its only when I am trying to PDF embedded charts that the problem occurs.

A snippet of the code I am using follows:

PSFileName = "C:\temp.ps"


If ActiveSheet.Name = "TN Graphs" Then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
Preview:=False, PrintToFile:=True, Collate:=True, _
ActivePrinter:="Acrobat Distiller", _
prtofilename:=PSFileName

Else

Range(ActiveSheet.PageSetup.PrintArea).PrintOut Copies:=1, _
Preview:=False, PrintToFile:=True, Collate:=True, _
ActivePrinter:="Acrobat Distiller", _
prtofilename:=PSFileName
End If

Set myPDF = New PdfDistiller

'... then converts the temp file to a PDF file
myPDF.FileToPDF PSFileName, PDFFileName, ""


Kill "c:\temp.ps" 'Deletes the temp file


Thanks in advance for your help - taduh