Advertisement

07.21.2008 at 06:07AM PDT, ID: 23581628
[x]
Attachment Details

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

Asked by taduh in VB Script, Adobe Acrobat, Microsoft Excel Spreadsheet Software

Tags: Adobe, 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.
Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:

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
[+][-]07.21.2008 at 06:17AM PDT, ID: 22050174

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.21.2008 at 06:21AM PDT, ID: 22050208

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.21.2008 at 06:54AM PDT, ID: 22050485

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.21.2008 at 06:58AM PDT, ID: 22050515

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.21.2008 at 07:24AM PDT, ID: 22050742

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.21.2008 at 07:36AM PDT, ID: 22050855

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.21.2008 at 07:46AM PDT, ID: 22050954

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.21.2008 at 08:18AM PDT, ID: 22051320

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.21.2008 at 11:33PM PDT, ID: 22056823

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: VB Script, Adobe Acrobat, Microsoft Excel Spreadsheet Software
Tags: Adobe, PDFDistiller
Sign Up Now!
Solution Provided By: Psychotec
Participating Experts: 2
Solution Grade: B
 
 
[+][-]07.22.2008 at 06:15AM PDT, ID: 22058962

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.22.2008 at 06:33AM PDT, ID: 22059122

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.22.2008 at 06:47AM PDT, ID: 22059274

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628