Opening Report Using VB in Business Objects

[MichaelRo1]

Hi,

I have been trying unsuccessfully to open a document/report using VB.

Have tried several methods:
1)Open "Report1" For Random As #1
'Can't see the report anywhere
2)Documents.Open ("C:\Report1.rep")
'Object variable or With block variable not set (Error 91)

Not sure what I'm doing wrong

Ideally I'd like to specify the Path Name to open several reports from the same folder refresh, print, save and close

ie Directory = Report1.Path

Any Ideas?

 

[xlbo]

Posted this in the Business Objects forum a while back - it opens a report, refreshes it and does Edit>Copy all for each report in the document to a new sheet in an excel workbook - you will need to set a reference to Business Objects in VBE>Tools>References:

Sub GetBOData()
Dim BoApp As busobj.Application, BODoc As busobj.Document, BORep As busobj.Report
Set BoApp = CreateObject("BusinessObjects.application")
With BoApp
    .LoginAs "username", "password"
    .Visible = True
[COLOR=green]'open file[/color]
    .Documents.Open ("w:\system\knowledge\z_finished\corporate volumes auto.rep")
    With .ActiveDocument
[COLOR=green]'refresh data[/color]
        .Refresh
        i = 1
[COLOR=green]'Loop through reports in document and "copy all" to excel[/color]
        For Each rpt In .Reports
            rpt.Activate
            BoApp.CmdBars(2).Controls("&Edit").Controls(20).Execute
            Sheets(i).Range("A1").PasteSpecial Paste:=xlPasteValues
            i = i + 1
        Next
    End With
End With
[COLOR=green]'Clear object variables[/color]
Set BoApp = Nothing
Set BODoc = Nothing
End Sub

Obviously, you will have to amend the "copy all" element to do the print and save instead but this should get you going

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[MichaelRo1]

Cheers Mate,

Managed to Open, Refresh Print and Save.

My only problem now a User Defined Prompt

The Report asks the user to define a date range - I don't want to remove this from the report but I don't want have to click ok everytime - Is there a way I can say ok in the macro?

 

[xlbo]

It's a bit of a pain but I use this - the prompts are part of the variables collection so this code loops through all the variables until it finds one of a specific name (the prompt) - it then fills it with an item from an array - you would want to enter a date instead:

iVarCount = myDoc.Variables.Count
    For j = 1 To iVarCount
        If myDoc.Variables.Item(j).Name = "Select BDM" Then
          myDoc.Variables.Item(j).Value = [b]BDMArray(i)[/b]
        End If
    Next j

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[MichaelRo1]

Ta for that

Also Managed to find an alternative

I found that BO retains the last prompted value
As the prompts won't really change - I turned them off

Application.interactive = false/true

 

[xlbo]

Fair enough - personally, I think that's leaving it a bit to chance but if it works for you then go for it

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[robcarr]

XLBO,

I want to use this coding to enter the month to date dates in the variables, but I am unsure how to do this, and where to do it, I would presume it would go after .refresh and before the report copying.

iVarCount = myDoc.Variables.Count
    For j = 1 To iVarCount
        If myDoc.Variables.Item(j).Name = "Select BDM" Then
          myDoc.Variables.Item(j).Value = BDMArray(i)
        End If
    Next j

there are only 2 variables that need to be updated "Enter form Date" and "Enter To Date", variable 3 is always the same.
Also how would I get it to accept the changes in the coding, without having to press "ok".

Thanks


Hope this is of use, Rob.

 

[xlbo]

Do the prompt stuff BEFORE you refresh
B/O retains the last prompt value (ie that which you have just entered)
set application.interactive = false
(so it doesn't prompt)
and then just refresh

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question