Copying contents of BO report to Excel

[mid181]

hi

I want to call a VBA macro to copy contents of BO report to excel when i choose run macro menu option from the report..
any sample code for starting on this...

TIA
mid181

 

[xlbo]

try this:

Sub GetBOData()
Dim XLApp As Excel.Application, XLWb As Excel.Workbook
Set XLApp = CreateObject("Excel.application&quot
With XLApp
set XLWb = .workbooks.add
end with
with activedocument
with .activereport
BoApp.CmdBars(2).Controls("&Edit&quot.Controls(20).Execute
end with
with XLApp.XLWb
.Sheets(1).Range("A1&quot.PasteSpecial Paste:=xlPasteValues
End With

Set XLApp = Nothing
Set XLWb = Nothing
End Sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[kprit]

Or May be try this.........

Dim BoApp As busobj.Application, BODoc As busobj.Document, BORep As busobj.Report
Set BoApp = CreateObject("BusinessObjects.application&quot

Dim objExcelApp As New Excel.Application
Dim objExcel As Object
'create Excel
Set objExcelApp = New Excel.Application
'open file
Set objExcel = objExcelApp.Workbooks.Open("c:\temp\booknew.xls&quot
objExcelApp.Visible = True
'Do stuff here
Set objExcel = Nothing 'clears memory
'as
With BoApp
'.LoginAs "Username", "password"
'.Visible = True
'.Documents.Open ("D:\Pritpal\Business Obj - Try\Document1.rep&quot

With .ActiveDocument
'.Refresh
i = 1
For Each rpt In .Reports
rpt.Activate
'use the copyALL function
BoApp.CmdBars(2).Controls("&Edit&quot.Controls(20).Execute
'paste to excel
Sheets(i).Range("A1&quot.PasteSpecial Paste:=xlPasteValues
i = i + 1
Next
End With
End With

BoApp.ActiveDocument.SaveAs ("D:\Pritpal\Business Obj - Try\Document1.pdf&quot
'set variables to nothing
Set BoApp = Nothing
Set BODoc = Nothing

 

[xlbo]

Kprit - That looks awful like some code I posted for interacting with BO FROM excel. If this was from BO to Excel, you wopuldn't need to declare the BO variables (BOApp etc)

You certainly wouldn'yt use this line:

Set objExcel = Nothing 'clears memory

in the middle of the code as it resets the excel variable to nothing

And this won't work as you would already be in Business Objects:
With BoApp
'.LoginAs "Username", "password"
'.Visible = True
'.Documents.Open ("D:\Pritpal\Business Obj - Try\Document1.rep&quot

Sorry but it looks like you have mixed up 2 bits of code - one for pushing data out to excel from BO and one for pulling data into excel, using BO as the extraction tool


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[kavimy]

I am facing a problem when I try to use the Copy all Command through VBA.

It throws the runtime error like "Automation Error The Server threw an Exception"

When we make the application in the visible mode and check; the menu item "Copy All" is disabled.

Can anyone help me in this regard

Thanks,
Kavitha

 

[xlbo]

Is CopyAll disabled when you go into Business Objects normally (manually) ?

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[kavimy]

No, when i open the Business objects it is enabled, but when we open the document through SDK it happens like this...

 

[xlbo]

Very bizarre - I can't really help you there as it all works fine when I open business objects through code....Could it be a function of the userID you are opening objects with via code ??

Could you post the code you are using ??

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[kavimy]

Below is the code that i tried

Public busapp As busobj.Application
Public boDoc As busobj.Document

Set busapp = CreateObject("BusinessObjects.Application&quot
busapp.LoginAs g_uid_s, g_pwd_s, False, g_dom_s
Set boDoc = busapp.Documents.Open(docnm, True, True, g_pwd_s, g_pwd_s)
busapp.Clipboard.Clear
busapp.ActiveDocument.Reports(1).Activate
busapp.CmdBars(2).Controls.Item("&Edit&quot.Controls.Item("Cop&y All&quot.Execute

 

[xlbo]

Can't really see anything wrong there - as I alluded to before - could it be a function of the userID that you are trying to log in as ?? Some userIDs may have only limited access to BO functionality....

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[kavimy]

User that I am using is the Supervisor, it has all the access.
But if we login in BO with the same user ID manually i am able to do Copy All

 

[xlbo]

I'm out of ideas in that case - maybe a look at the BO knowledgebase might throw some light on the issue....

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244