Generating a PDF file

system · October 23, 2002, 1:49pm

Hi All!

Right now, I am hard coding the report tab names in my macro while generating a PDF file, Is it possible to read the value from a particular cell and tab page in a report?

Example:

[edit by Eileen to add BBCode]

doc.Reports.Item(2).Activate
Set rep = doc.ActiveReport
rep.Name = "1.Total Client Value Summary"

doc.Reports.Item(3).Activate
Set rep = doc.ActiveReport
rep.Name = "2.Client Value Detail by Product"   
etc..

Thanks,

Ravi Amara (BOB member since 2002-10-02)

system · October 23, 2002, 2:03pm

look at Export Report Tabs to MS Excel for a code sample that walks through all reports for a document…

Sample:

Set Doc = ActiveDocument 
For i = 1 To Doc.Reports.Count 
  Set Rep = Doc.Reports.Item(i) 
  Rep.ExportAsText (Path &amp; Rep.Name &amp; ".txt") 
Next i

Is that what you are looking for?

Andreas (BOB member since 2002-06-20)

system · October 23, 2002, 2:18pm

You can read the name of a tab page, but not a cell (that I know of)… I got around this once by creating variables with the information I needed… The variable started with the file extension wanted for the Save As (PDF, TXT, etc) and the variable “formula” was the name of the report tabs to be saved out.

Hope this helps/gets you thinking… Good luck!

JennFisher (BOB member since 2002-06-25)

system · October 23, 2002, 2:20pm

There’s a resolution on the Knowledge Base if you have access to it. This resolution is for VBA. What we found was that with VBA you can save all of the tabs into one PDF. With JSP you can only save them out at separate PDFs.

Here’s the other topic where we discussed some of the issues and resolutions.

Eileen King (BOB member since 2002-07-10)

system · October 23, 2002, 3:28pm

Hi Andrea,

Sorry for being not clear in my question. I want to export the BO report(with some 14 tabs) as one pdf file. if I don’t hard code the report names like I mentioned above, the Bookmarks of the PDF file will be just 1,2,3…14.

So, my question is, Is it possible to read the names of the each tab page? I have all the names of the tab pages, in the HOME tab page in 14 different cells! If I can read each cell of the HOME tab page, my work will be done!

Hope I am clear this time

Ravi Amara (BOB member since 2002-10-02)

system · October 23, 2002, 3:34pm

Just read the name of the tabs:


  Dim boDoc As Document
  Dim boRpts As Reports
  Dim boRpt As Report
  Dim x As Integer
  Dim RptName(100) As String
      
  Set boDoc = ActiveDocument
  Set boRpts = boDoc.Reports
  
  For x = 1 To boRpts.Count
     Set boRpt = boRpts.Item(x)
     RptName(x) = boRpt.Name
  Next x

That’ll store up to 100 report (tab) names for you in the RptName array…

Good luck!

JennFisher (BOB member since 2002-06-25)

system · October 23, 2002, 3:34pm

This seems to be a good solution. But, let me understand this…
If there are 14 tab pages, there will be 14 variables to retrieve the tab page names…

So, Again, I need to mention each variable in my macro, right? I cannot keep them in a loop…

Ravi Amara (BOB member since 2002-10-02)

system · October 23, 2002, 3:37pm

I am not sure if I understand…

Here is the loop (looping through all report names in a BO document, may it be 5 report tabs or 50 report tabs).

For x = 1 To boRpts.Count 
     Set boRpt = boRpts.Item(x) 
     RptName(x) = boRpt.Name 
  Next x

Andreas (BOB member since 2002-06-20)

system · October 23, 2002, 3:38pm

Ignore my variable suggestion earlier. All the names will be within your macro as RptName(#)… You can refer to them later in the code with another loop if you need.

JennFisher (BOB member since 2002-06-25)

system · October 23, 2002, 3:39pm

IF my Tab page names are 1,2,3,4…And, the only place I can see the report tab page names is in a cell, then what to do?

Ravi Amara (BOB member since 2002-10-02)

system · October 23, 2002, 3:40pm

Nothing. You can’t read a cell (that I know of). You could create a variable and put that in the cell and then read the formula of the variable, but you can’t read a cell.

But isn’t that point moot if you are just trying to get the report tab names??

JennFisher (BOB member since 2002-06-25)

system · October 23, 2002, 3:43pm

Andreas:

I am not sure if I understand…

Here is the loop (looping through all report names in a BO document, may it be 5 report tabs or 50 report tabs).
For x = 1 To boRpts.Count 
     Set boRpt = boRpts.Item(x) 
     RptName(x) = boRpt.Name 
  Next x 

Sorry Andrea, that response is for Jennifer’s reply! From now onwards, I have to reply by clicking on “quote” rather than a simple “reply”!

Ravi Amara (BOB member since 2002-10-02)

system · October 23, 2002, 3:49pm

In both the ways, either hard coding the report names or by using a variable, my code will not become small… same code repeated for 14 times

Ravi Amara (BOB member since 2002-10-02)

system · October 23, 2002, 3:52pm

:? I’m sorry, txamara… I don’t understand what the issue is. By using the loop you have a small amount of code that will give you all the report (tab) names in your macro. I don’t know what else to say to help you. I’m sorry.

JennFisher (BOB member since 2002-06-25)

system · October 23, 2002, 3:53pm

Why can’t you use the report name showing up in the report tab (then you would include your code ONCE within the For…Next loop)? Why do you have to reference a cell?

For Example:

Dim boDoc As Document 
  Dim boRpts As Reports 
  Dim boRpt As Report 
  Dim x As Integer 
  Dim RptName(100) As String 
      
  Set boDoc = ActiveDocument 
  Set boRpts = boDoc.Reports 
  
  For x = 1 To boRpts.Count 
     Set boRpt = boRpts.Item(x) 
     RptName(x) = boRpt.Name 
     Include your code for report here (exporting to PDF or whatever)
  Next x

Andreas (BOB member since 2002-06-20)

system · October 23, 2002, 4:10pm

Let me show you the code…

Sub RepPDFGenerate(doc As busobj.Document, ByVal Filename As String, NodeID As String, NodeName As String)
    Dim rep As Report
    Dim x As Boolean
    Dim n As Integer
    
    Call CheckDir(Filename)
    
    doc.Reports.Item(1).Activate
    Filename = Filename &amp; (999999999 - NodeID) &amp; "\default"
    
    Set rep = doc.ActiveReport
    
    doc.Reports.Item(2).Activate
    Set rep = doc.ActiveReport
    rep.Name = "1.Client Value Monthly and by Product"
    
    doc.Reports.Item(3).Activate
    Set rep = doc.ActiveReport
    rep.Name = "2.Client Value by Person Business Unit and by Office"
    
    doc.Reports.Item(4).Activate
    Set rep = doc.ActiveReport
    rep.Name = "3.Overall Top 30 Clients by Month"
    
    doc.Reports.Item(5).Activate
    Set rep = doc.ActiveReport
    rep.Name = "4 Top 30 Clients across Product"
    
    doc.Reports.Item(6).Activate
    Set rep = doc.ActiveReport
    rep.Name = "5. Top 30 Clients by Salesperson"
    
    doc.Reports.Item(7).Activate
    Set rep = doc.ActiveReport
    rep.Name = "6. Top 30 Clients for Specific Product"
    
    doc.Reports.Item(8).Activate
    Set rep = doc.ActiveReport
    rep.Name = "7. Client Value by Salesperson Monthly and by Product"
    
    doc.Reports.Item(9).Activate
    Set rep = doc.ActiveReport
    rep.Name = "8. Individual Salesperson Report"
  
     
    doc.ExportAsPDF (Filename)
    log1.LogIt "Document saved as " &amp; Filename &amp; ""
    
    doc.Reports.Item(2).Activate
    Set rep = doc.ActiveReport
    rep.Name = "1"
    
    doc.Reports.Item(3).Activate
    Set rep = doc.ActiveReport
    rep.Name = "2"
    
    doc.Reports.Item(4).Activate
    Set rep = doc.ActiveReport
    rep.Name = "3"
    
    doc.Reports.Item(5).Activate
    Set rep = doc.ActiveReport
    rep.Name = "4"
    
    doc.Reports.Item(6).Activate
    Set rep = doc.ActiveReport
    rep.Name = "5"
    
    doc.Reports.Item(7).Activate
    Set rep = doc.ActiveReport
    rep.Name = "6"
    
    doc.Reports.Item(8).Activate
    Set rep = doc.ActiveReport
    rep.Name = "7"
    
    doc.Reports.Item(9).Activate
    Set rep = doc.ActiveReport
    rep.Name = "8"
    
    doc.Reports.Item(1).Activate
    Set rep = doc.ActiveReport
    
    log1.LogIt ""
    log1.LogIt "Tab saved as " &amp; Filename &amp; ", for: " &amp; NodeName
End Sub

The problem is our report tab pages are named as 1,2,3,4,5,6,7 and 8. The actual names of the report tabs are in 8 individual cells, in the HOME tab page. As there is no way to read the cell, I am hard coding like the code shown above. I want to minimize the coding…

Any suggestions??

Ravi Amara (BOB member since 2002-10-02)

system · October 23, 2002, 4:13pm

Gotcha. Easy answer: rename your tabs. Why not?

Not easy answer: Create a variable for each report name… So the variable Rpt Name 1 has the formula 1.Client Value Monthly and by Product. Then you can read in the values of those variables and use those in the code.

Hope this helps!

JennFisher (BOB member since 2002-06-25)

system · October 23, 2002, 4:24pm

Cannot rename the tabs as per our …!

How Can I make it generic? We are using this fuction for several reports

Ravi Amara (BOB member since 2002-10-02)