Copy BO report to Excel via VBA 1

[kfizz]

Hello,

please I need your help.
I use a code from your Forum but I get a lot of error messages.

Dim BoApp As busobj.Application, BODoc As busobj.Document, BORep As busobj.Report
Dim i, rpt
Set BoApp = CreateObject("BusinessObjects.application")
With BoApp
    .LoginAs "xxxxxx", "xxxxxx"
    .Visible = True
    .Documents.Open ("c:\1_Sales_from_SMARTS_11_daily.rep")
   ' .Documents.Open ("Report Path & Name.rep")
    With .ActiveDocument
        '.Refresh
        i = 1
        For Each rpt In .Reports
            rpt.Activate
            'use the copyALL function
            BoApp.CmdBars(2).Controls("&Edit").Controls(20).Execute
            'paste to excel
            
            Sheets(i).Range("A1").PasteSpecial Paste:=xlPasteValues
            i = i + 1
        Next
    End With
End With
'set variables to nothing
Set BoApp = Nothing
Set BODoc = Nothing

If I start the code the first error message is:
"The PastSpecial Methode of the Range-Objects can't execute"
If I push debuggen then has the Paste:=xlPasteValues the Value -4163
The Business Object report is open and I see the data.

What make I wrong? Sorry for my bad english, I'm a German.

Thank you for your help!
Ken

 

[SkipVought]

hi,

Can you stop the code just after you execute the copyALL feature in BO, and determine if the intended data is in your ClipBoard by, for instance, actually pasteing something into a sheet or a Notepad?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kfizz]

Hello SkipVought,

a touch of genius...yes it is empty but why because I can see the data by the report.

Ken

 

[kfizz]

Hi,

I found a solve for my problem:

Sub Test()
Dim oBOSession As busobj.Application
    
    Set oBOSession = New busobj.Application
    Call oBOSession.LoginAs("xxxxx", "xxxxx")
    Call oBOSession.Documents.Open("c:\your report.rep")
    Call oBOSession.ActiveDocument.ActiveReport.ExportAsText("c:\file.xls")
    Set oBOSession = Nothing
    MsgBox "done"
End Sub

It works in Excel or Access.

 

[kfizz]

Hi,

I forgot:

oBOSession.Application.Interactive = False ' no questions
oBOSession.Application.Quit
and if you write: Call oBOSession.LoginAs("xxxxx", "xxxxx",0)'the 0 is new
you can write the name and the password.

Thank you!

 

[SkipVought]

@kfizz, thx for sharing your solution.

I am a huge Excel guy, but our company is implementing an SAP ERP system with BO for reporting. We have pushed back the first go live due to lack of adequate preparation and the next go live looms in the months to come. The BO universes seem also to lack completeness and I am in dread of the day of reckoning.

I suppose I'll be much more involved with BO forums when that time actually comes.

Thx again for your contribution. I archived this thread, as it has good stuff that you provided as your solution!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, what is the exact library name that you set in references?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kfizz]

Hi Skip,

I use businessObjects 6.5 Objekt Libary

ken