asked on 7/21/2008

How to code in VBA to print an embedded Excel chart using PDFDistiller

Good Morning Experts!
I have used some VBA code which I got from Experts Exchange to print Excel spreadsheet reports through the PdfDistiller. Now I want to print an embedded chart, but I find that I cannot get the same code I use to print the Excel report to work for printing the chart. I have tried to modify the code to be more chart-specific. What happens, when I run the attached code (the portion of the IF statement relating to TN Graphs), is that the C:\temp.ps file is created. If I double-click on it, it will create the chart in a PDF, but I need it to do this in an automated fashion using VBA code.

Sub Print_PDF_Rpt()
 
 
Dim PSFileName As String
Dim PDFFileName As String
Dim MySheet As Worksheet
Dim myPDF As PdfDistiller
 
Dim I As Integer
 
'Create PDF reports
            
    Set myPDF = Nothing
    
    'converts that to a PDF file.
    
    PSFileName = "C:\temp.ps"
    
    'Determine which column to get the report file name from
    If ActiveSheet.Name = "Trend for Print" Or ActiveSheet.Name = "TN Graphs" Then
        PDFFileName = PrintList.range("G" & x) & ".pdf"
    ElseIf ActiveSheet.Name = "NIR Current" Or ActiveSheet.Name = "NIR LPO" Then
        PDFFileName = PrintList.range("I" & x) & ".pdf"
    ElseIf ActiveSheet.Name = "bounce safe" Then                             
        If range("Typerun") = "Standard" Then                                
            PDFFileName = range("BncSfPath") & ".pdf" 'Standard run          
        Else                                                                 
            PDFFileName = range("BncSfPathAH") & ".pdf"  'Ad Hoc run         
        End If                                                               
    ElseIf ActiveSheet.Name = "NSF" Then                                     
        If range("Typerun") = "Standard" Then                                'x
            PDFFileName = range("NSFPath") & ".pdf"  'Standard run           
        Else                                                                 
            PDFFileName = range("NSFPathAH") & ".pdf"  'Ad Hoc run           
        End If                                                               
    End If
                
 
                
    'The way the PDFdistiller works, it prints first to a temp file...
    If ActiveSheet.Name = "TN Graphs" Then
       'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True,
        ActiveChart.PrintOut Copies:=1, Collate:=True, _
        Preview:=False, PrintToFile:=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
    
    
End Sub

Open in new window

VB Script Microsoft Excel Adobe Acrobat

Last Comment

taduh

7/22/2008

ExcelGuide

7/21/2008

maybe that does not happen because the PDFFileName is empty....at the end of your code try and reply the msgbox here:

Set myPDF = New PdfDistiller
msgbox PDFFileName
    '... then converts the temp file to a PDF file
    myPDF.FileToPDF PSFileName, PDFFileName, ""

Open in new window

Karl Heinz Kremer

7/21/2008

Are you familiar with the Acrobat SDK? The Distiller API Reference contains information about how to specify a target filename (it's on page page 15 "Disabling prompts for output filenames). It requires that you set a registry key every time before you print to the Adobe PDF printer. When you do that, there is no need to first create a PostScript file and then in a second step manually call Distiller.

taduh

7/21/2008

ASKER

Psychotec,
I'm sure that the PDFFileName is empty, but that has not affected the code that prints the reports.

khkremer,
I am not familiar with the Acrobat SDK. Where/how do I access it?

Thank you both,

Taduh

Karl Heinz Kremer

7/21/2008

http://www.adobe.com/devnet/acrobat/
Click on the "Documentation" tab and then scroll down until you find the Distiller API Reference (or download the whole SDK).
You may have to create a free account on Adobe's web site - I don't know how they are handling downloads these days.

ExcelGuide

7/21/2008

that is why you dont get the file, so try the following code and see what happens (wont affect your code when you do this with reports):

Set myPDF = New PdfDistiller
if PDFFileName = "" then
PDFFileName = "C:\test.pdf"
end if
    '... then converts the temp file to a PDF file
    myPDF.FileToPDF PSFileName, PDFFileName, ""

Open in new window

taduh

7/21/2008

ASKER

khkremer,

We are on Acrobat 5.0 (Windows) here. The only SDK offered for that version of Acrobat in the SDK and Downloads is for UNIX.

Any suggestions?

Thanks,

Taduh

Karl Heinz Kremer

7/21/2008

Besides upgrading to something more recent? No, unfortunately not. That registry key was introduced in Acrobat 7, so it's not an option for you.

taduh

7/21/2008

ASKER

Psychotec,

Actually, the PDFFilename is not empty. It gets populated in the following code:

If ActiveSheet.Name = "Trend for Print" Or ActiveSheet.Name = "TN Graphs" Then
PDFFileName = PrintList.range("G" & x) & ".pdf"

I proved this with your code.

The problem lies somewhere in this line of code:

myPDF.FileToPDF PSFileName, PDFFileName, ""

It just won't put the contents of PSFilename into a PDF format in PDFFilename. In my original post, I alluded to the fact that it does create PSFilename (C:\temp.ps) and when I click on this postscript file, it creates the pdf file. I'm just at a loss for how to get it to create the PDF through VBA, especially since the code works for reports - just not for embedded charts.

Any other suggestions? - I've increased the points to the max (500).

Thanks,

taduh

ASKER CERTIFIED SOLUTION

ExcelGuide

7/22/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

taduh

7/22/2008

ASKER

Psychotec,

Its giving me an error message: "Unable to get the Text property of the ChartTitle class"

Also, why should it matter what the PDFfilename is?

Thanks

taduh

ExcelGuide

7/22/2008

could you gibve it a try and replace
If ActiveSheet.ChartObjects(1).Chart.ChartTitle.Text <> "" Then
PDFFileName = "C:\" & ActiveSheet.ChartObjects(1).Chart.ChartTitle.Text & ".pdf"
End If

into

PDFFileName = "C:\test.pdf"

It should matter so the code knows into what file he should convert....

taduh

7/22/2008

ASKER

I'll try it, but the code already does know what file to convert into by the following lines:

If ActiveSheet.Name = "Trend for Print" Or ActiveSheet.Name = "TN Graphs" Then
PDFFileName = PrintList.range("G" & x) & ".pdf"

taduh

taduh

7/22/2008

ASKER

Psychotec,

I'm awarding the points to you because had I followed your advice I probably would have found out was going on earlier. The problem (DUH!) was that I was sending the reports to a certain directory, and the charts were to go to a sub-directory of that directory, but on my test PC, I had not setup the sub-directory. I had forgotten about it and was looking for the PDF files to appear in the main directory. Since the sub-directory the PDF files were to go to did not exist, the FileToPDF statement simply did not create the PDF files. This is a good example of why I chose the handle, Ta-DUH! Thanks for your help.