Saving & Printing report using VBA as PDF.

sdk development
1

Hi All,

Can anyone tell me the VBA Code to save and print a report by branches, e.g.: i have 10 branches in a country, 10 reports must be saved as pdf in a defined location and should print 10 different reports.

This might be very simple, but i am very new to BO & VBA.

Your help will be highly appreciated.
Thanks

wasay. :?:

wasy.wasu :us: (BOB member since 2006-09-18)

2

How is your report formatted? You can use the ExportasPDF() method on a document object to save the whole REP for the same method on a report object to save only the one report tab. Depending on how things are laid out and how youā€™re going to dsitribute things, you probabaly have a lot of options.

-Steve

srwhisen :us: (BOB member since 2006-11-27)

3

HI,

You can do this in two ways. One is to create a report with each brances in seperate tab and then use the macro ExportAsPDF() to save it to different reports.
The other way is to use the field which is used for Branches as section in the report and then use the macro to save the report into diff PDFs.

Tell us which one you prefer and we can help you out.

Raja Saripalli :us: (BOB member since 2005-12-14)

4

Raj,

Weā€™ve exported entire documents into one PDF and entire reports into several PDFs, but Iā€™m intrigued by your observarion that separate sections (in the same report?) could be saved to individual PDFs. How would you do that?

srwhisen :us: (BOB member since 2006-11-27)

5

u-said.

HI,

You can do this in two ways. One is to create a report with each brances in seperate tab and then use the macro ExportAsPDF() to save it to different reports.
The other way is to use the field which is used for Branches as section in the report and then use the macro to save the report into diff PDFs.

Tell us which one you prefer and we can help you out.

Regards
Raj

Hi Raj,

As you have mentioned, i would like to use the second way u suggestedā€¦to use the field which is used for Branches as section in the report and then use the macro to save the report into diff PDFs.

Appreciate your efforts, please let me know how to do itā€¦

Regards,
Wasay.

wasy.wasu :us: (BOB member since 2006-09-18)

6

Hi

Here is the Macroā€¦In the macro ā€œStateā€ is the variable that is placed in the section of the report. You can change that with whatever field you wnat to be in your report. The PDF files will be saved for different section values.

Option Explicit
Sub FilterAndExport()
  Dim mydoc As Document
  Dim myrpt As Report
  Dim myFilterVar As DocumentVariable
  
  Dim i, j, noOfReports, intNumChoices As Integer
  Dim myFilterChoices As Variant
  Dim strNextValue As String
  
  'Active Document
  Set mydoc = ActiveDocument
  
  ' Active (with focus) Report
  Set myrpt = ActiveReport
 
  ' Put your variable (or query object) here
  Set myFilterVar = mydoc.DocumentVariables("[b]State[/b]")
  
  ' find out how many values there are for the variable
  intNumChoices = UBound(myFilterVar.Values(boUniqueValues))
  ' collect the number of choices in a variant variable
  myFilterChoices = myFilterVar.Values(boUniqueValues)
  For i = 1 To intNumChoices
   
    ' Get the variable value
    strNextValue = myFilterChoices(i)
    
    noOfReports = mydoc.Reports.Count
    For j = 1 To noOfReports
    Set myrpt = mydoc.Reports.Item(j)
    
    ' build filter
    myrpt.AddComplexFilter myFilterVar, "=<[b]State[/b]> = " &amp; """" &amp; strNextValue &amp; """"
    
    ' recompute the report
    myrpt.ForceCompute
    
    Next j
    
    'Declare Directory Structure
   Dim DocPDF_Dir As String
  'Set Directory Structure
  'DocPDF_Dir = "C:\Documents and Settings\Desktop\"
  DocPDF_Dir = "C:\Documents and Settings\Desktop\"

  'Declare Filenames
  Dim Document_Name As String
  'Set Filenames
  Document_Name = "2006" &amp; "_" &amp; strNextValue
  'Distribute Files
  ActiveDocument.ExportAsPDF (DocPDF_Dir &amp; Document_Name)
      
  Next i
  ' now, unless I can find a RemoveComplexFilter function, this line is used
  ' to invoke a filter that is always true.
  
  myrpt.AddComplexFilter myFilterVar, "=(1=1)"
  myrpt.ForceCompute
End Sub

Let us know if you still have some doubts

Raja Saripalli :us: (BOB member since 2005-12-14)

7

Hi Raj,

It works, your codes worked. it gave me the exact result as expected.
Thank you very much for your help. i will be intouch.

Regards,
Wasay

wasy.wasu :us: (BOB member since 2006-09-18)

8

You are welcome!! 8)

Raja Saripalli :us: (BOB member since 2005-12-14)

9

Thanks very much for this, Iā€™ve been using this code too!

Now Iā€™ve been trying to implement this code in another report as well, but the variable Iā€™m using there in the complexfilter is not a dimension (not a variable from the database), but itā€™s a ā€˜detail-variableā€™. Can this be done with such a complex filter as well?

What Iā€™m trying to achieve is that for each 500 pages a new PDF is created. These files have to be printed but our facilities donā€™t want files bigger than 500 pages (printer doesnā€™t handle moreā€¦).

Any help would be very much appreciated. This problem is giving me headaches :?

Koen

Edit: If I have to use the AddComplexFilter with a Dimension I could use it on the sections. In that case, I need several PDFā€™s which hold 160 sections each. Could this be an option writing the macro? (Iā€™m not really experienced using VBA yetā€¦)

Koen (BOB member since 2006-08-09)

10

Hi - it looks like this code should do just what I need but I am having some trouble getting it to recognise my section variable. I have secyioned my tab on "Segment Lvl2(Query 2 with CurrUnv)ā€™ but when I replace your bit of code with this

Set myFilterVar = mydoc.DocumentVariables(ā€œSegment Lvl2(Query 2 with CurrLead)ā€)

I get ā€œCannot find the Variable by its nameā€ back from VB.

The report is using 2 data providers (thus the variable name) - I have tried variations on the name eg just Segment Lvl2, enclosing it in <> etc to no avail.

Any clues? Afraid my VB is not too crash hot so probably something simple I have done.

Thanks - Jon

jonh (BOB member since 2002-12-18)

11

Assuming that you need to get to the inner most section of your report, try using this:

Dim objDoc As busobj.Document
Dim objDocVar As busobj.DocumentVariable
Dim objReport As busobj.Report
Dim objSection As busobj.SectionStructure

Dim strSectionMaster As String

Set objDoc = Application.ActiveDocument
Set objReport = objDoc.Reports.Item("<report name>")
Set objSection = objReport.GeneralSectionStructure

' find innermost section
Do Until objSection.IsTerminal
    Set objSection = objSection.SubSectionStructure
Loop

'get variable name that the section is created on
strSectionMaster = objSection.Master.Name

Hope it helps!

SB8 (BOB member since 2005-05-02)

12

Hi Raja ,

Thanks for above post regarding PDF report by Using Macro its helped alot
and its working fine for PDF format.

But my client need in excel format. Is there any way to generate report in Excel format using above code insteade of PDF.

Pls advice me and do needfulā€¦

Regards,
Sridhar Reddy

chanti_mps (BOB member since 2009-12-10)

13

works great!!!

except for 1 thing:

myrpt.AddComplexFilter myFilterVar, ā€œ=(1=1)ā€

==> in order to remove the filterā€¦ it doesnā€™t workā€¦ someone having an idea?

MichM3 (BOB member since 2005-06-07)

14

Dear,

I altered the VBA code above for an export to Excel.
I also added a Sub to the code in order to include a Picture (Companylogo for example) to the Top-Left of each Excel Sheet.

Unfortunatelly I receive the error: ā€œmethod range of object _global failedā€ which is probably due to my code not being 100% solid (Iā€™m not a dedicated VBA programmer). Maybe someone can enhance my code in order to operate more robuste.


.....

 'Declare Directory Structure
   Dim Doc_Dir As String
  'Set Directory Structure
  Doc_Dir = "<yourPath>"

  'Declare Filenames
  Dim Document_Name As String
  'Set Filenames
  Document_Name = "<yourDocName>" &amp; strNextValue
  
  ' Distribute Files
  ' PDF
  ActiveDocument.ExportAsPDF (Doc_Dir &amp; Document_Name)
  ' Excel
  ActiveDocument.SaveAs (Doc_Dir &amp; Document_Name &amp; ".xls")
  
  'Insert Company logo in Excel sheets
  ExcelInsertPicture noOfReports, Doc_Dir, Document_Name
      
  Next i
  ' now, unless I can find a RemoveComplexFilter function, this line is used
  ' to invoke a filter that is always true.
  
  'myrpt.AddComplexFilter myFilterVar, "=(1=1)"
  'myrpt.ForceCompute
  
End Sub


Private Sub ExcelInsertPicture(ByVal noOfSheets As Integer, ByVal DocPath As String, ByVal DocName As String)

' Define Excel attributes
Dim xlApp As Excel.Application, xlWB As Excel.Workbook, xlWS As Excel.Worksheet
Dim k As Integer
' Path to Picture
Dim pPath As String
pPath = "<yourCompanyLogoPath>"

Set xlApp = New Excel.Application

With xlApp

    'Open Workbook
    Set xlWB = .Workbooks.Open(DocPath &amp; DocName &amp; ".xls", ReadOnly:=False, Editable:=True)
    
    'Loop through all Sheets
    For k = 1 To noOfSheets
    
    'Activate appropriate Sheet
    Set xlWS = xlWB.Worksheets(k)
    xlWS.Activate

    'Insert picture in Sheet
    xlWS.Pictures.Insert (pPath)
    
    'Align picture Top-Left
    With xlWS.Pictures
        .Left = Range("A1").Left
        .Top = Range("A1").Top
    End With
    
    Next k
    
End With

'Activate 1st Sheet before saving
xlWB.Worksheets(1).Activate

'Save and Close Workbook
xlWB.Save
xlWB.Close

Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Set xlWS = Nothing

End Sub

the Error occurs on line:

.Left = Range(ā€œA1ā€).Left

It has to do with the Range that is not correctly definedā€¦ someone who can help me with this?

MichM3 (BOB member since 2005-06-07)

15

anyone? :cry:

MichM3 (BOB member since 2005-06-07)

16

this one is solved:

I had to declare the range, by adding ā€œxlWS.ā€ before the .Range


.Left = xlWS.Range("A1").Left 
.Top = xlWS.Range("A1").Top

now it works fine!

MichM3 (BOB member since 2005-06-07)

17

Probably an easier solution is to simply have the form open with a focus in your db window, then choose Print from the File menu . If you have a printer driver that prints PDF files, you can print an exact copy of this form in a PDF file.Or you can use the Microsoft Document Image Writer to generate a file. tif, or you can print the same thing on the paper with all printers installed on your computer.

ralleydesew (BOB member since 2011-02-18)