Extracting data from Business Objects to Excel using VBA

[Ian Bartlett]

All,

I'm running Win 7, Excel 2010 and Business Objects 6.5.

I'm attempting to run a VBA script in Excel to start Business Objects, open a file, refresh it and then extract the data into Excel.

What I have so far, working fine, is this:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Open_BO_and_Rep()<br>****<br>****<SPAN style="color:#00007F">Dim</SPAN> BOApp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br>****<SPAN style="color:#00007F">Set</SPAN> BOApp = CreateObject("BusinessObjects.application")<br><br>****<SPAN style="color:#00007F">With</SPAN> BOApp<br>********.Visible = <SPAN style="color:#00007F">True</SPAN><br>********.LoginAs "Username", "Password"<br>********.Documents.Open ("E:\Graph Book.rep")<br>********<br>********<SPAN style="color:#00007F">With</SPAN> .ActiveDocument<br>************.Refresh<br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>****<br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

This opens BO & the required file, and refreshes it. So far, so good.

My problem lies with the next step, of getting the data from the refreshed BO report into Excel.

I found this code:

<font face=Courier New>****<SPAN style="color:#00007F">With</SPAN> .ActiveDocument<br>********.Refresh<br>********i = 1<br>********<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rpt <SPAN style="color:#00007F">In</SPAN> .Reports<br>****************rpt.Activate<br>****************BOApp.CmdBars(2).Controls("&Edit").Controls(20).Execute<br>****************Sheets(i).Range("A1").PasteSpecial Paste:=xlPasteValues<br>************i = i + 1<br>********<SPAN style="color:#00007F">Next</SPAN><br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>

which should select all / copy all in the BO report, and then paste it into Excel. I can't get it to work. I receive the error "Paste Special method of range class failed" at the "Sheets(i).Range("A1").PasteSpecial Paste:=xlPasteValues" line.

I found references to "adding BO to the Excel library", but I couldn't find how to do this and I'm not sure if this is the cause.

Could anyone help please?

Ian

 

[Ian Bartlett]

Repost with cleaned up code - anyone able to assist?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Get_BO_Data()<br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> BOApp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br>    <SPAN style="color:#00007F">Set</SPAN> BOApp = CreateObject("BusinessObjects.application")<br><br>    <SPAN style="color:#00007F">With</SPAN> BOApp<br>        .Visible = <SPAN style="color:#00007F">True</SPAN><br>        .LoginAs "Username", "Password"<br>        .Documents.Open ("E:\Graph Book.rep")<br>        <SPAN style="color:#00007F">Call</SPAN> rpt.ExportAsExcel("E:\Graph Book.xls")<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br>    <SPAN style="color:#00007F">Set</SPAN> BOApp = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

and not working:

<font face=Courier New>    <SPAN style="color:#00007F">With</SPAN> .ActiveDocument<br>        .Refresh<br>        i = 1<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rpt <SPAN style="color:#00007F">In</SPAN> .Reports<br>                rpt.Activate<br>                BOApp.CmdBars(2).Controls("&Edit").Controls(20).Execute<br>                Sheets(i).Range("A1").PasteSpecial Paste:=xlPasteValues<br>            i = i + 1<br>        <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>

Thanks,

Ian

 

[Ian Bartlett]

I found the code that I needed to save the BO report as a spreadsheet, hope it's of use to someone else:

<font face=Courier New>    <SPAN style="color:#00007F">Call</SPAN> BOApp.ActiveDocument.ActiveReport.ExportAsText("E:\Graph Book Data.xls")<br></FONT>

Thread closed.

Ian