Printing to PDF using distiller

[cfoye130]

I have Adobe Distiller 8.0. Using Excel '07.

The following code runs fine, but the pdf output that occurs is not a pdf but rather a log file stating:

%%[ Error: undefined; OffendingCommand: E*t600R&u600D*r0F&l1o&l2A&l7H&l0S&l1X&l8c1E*p0x0Y*c0t7632x5880Y*b0M ]%%
%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%

There is lots on the internet about the error, but I can't find a solution. I need to use distiller rather than Excels PDF solution, because the PDF solution from microsoft messess up the format of the charts where distiller does not. Any help would be greatly appreciated. Thanks!

Sub InFlightUpdate()
    Dim I As Integer
    Dim wbJIRA As Workbook
    Dim wbResults As Workbook
    Dim wbInflight As Workbook
    Dim wbInflightLoc As String
    Dim InProcessFile As String
    Dim wbInflightLoc2 As String
    Dim PDFStartLoc As String
    Dim PDFEndLoc As String
    Dim PDFSheet As Worksheet
    Dim oPDF As PdfDistiller
    Dim TmpPSFile As String
 
    InProcessFile = "H:\Inprocess.xlsx"
 
    I = 2
    Do Until IsEmpty(Sheet2.Cells(I, 1))
'Action if effort is active, but not in test
        If Sheet2.Cells(I, 11).Value = 1 Then
            wbInflightLoc = Sheet2.Cells(I, 2).Value
            wbInflightLoc2 = Sheet2.Cells(I, 8).Value
            PDFStartLoc = Sheet2.Cells(I, 9).Value
            PDFEndLoc = Sheet2.Cells(I, 10).Value
            Name PDFStartLoc As PDFEndLoc
            Set wbInflight = Application.Workbooks.Open(wbInflightLoc)
                wbInflight.Sheets(2).Cells(75, 2).Value = Sheet2.Cells(I, 6).Value
                Kill InProcessFile
                wbInflight.SaveAs (InProcessFile)
                wbInflight.Close True
                Kill wbInflightLoc2
            Set wbInflight = Application.Workbooks.Open(InProcessFile)
                Set PDFSheet = ActiveSheet
                Set oPDF = New PdfDistiller
                TmpPSFile = "H:\TmpPSFile.ps"
                PDFSheet.PrintOut copies:=1, preview:=False, _
                ActivePrinter:="Acrobat Distiller", printtofile:=True, _
                collate:=True, PrToFileName:=TmpPSFile
                oPDF.FileToPDF TmpPSFile, PDFStartLoc, ""
                Kill TmpPSFile
                wbInflight.SaveAs (wbInflightLoc2)
                wbInflight.Close False
        Else
            'do nothing
        End If
'Action if effort is active, and in test
        If Sheet2.Cells(I, 11).Value = 2 Then
            'code to come
        End If
 
        I = I + 1
        Set wbJIRA = Nothing
        Set wbResults = Nothing
        Set wbInflight = Nothing
    Loop
End Sub

 

[mole999]

failed to read the post fully (about saving as PDF) oops

cut

 

[cfoye130]

I previously stated I have to use Distiller because saving to pdf directly from Excel messes up the location and format of the charts on the spread sheet.