Importing BO Data to excel using VBA

system · March 24, 2009, 3:56pm

Hi.
I’d like to know if there’s a way to write code in Visual Basic For Excel that will run a BO query and import the data to an excel sheet.
I’ve noticed that in VB for excel => Tools => References there are some instances refering to BO. Is it possible to use those for my purpose?
I have designer permissions for the universe i want to take the data from.
I’ll appreciate any comments on the subject.
Thanks
Rafael
BO References in VB.JPG

Rafaelz (BOB member since 2007-10-28)

system · March 25, 2009, 6:07am

But I guess you will better off exporting data from a BO report(created off a universe) to Excel (and use any macros in BO if need be)

.

haider (BOB member since 2005-07-18)

system · March 25, 2009, 12:43pm

Hi haider

Thank you for your reply.
My purpose is to automatize things as much as possible.
i was wondering if it is possible to write a VB macro in excel that will access the report and transfer it back to an excel sheet.
Also if i can import the data to excel by writing a macro in BO that will take the data from the report and export it to excel i’d be happy if you tell me how to begin this task (Where in BO can i write and run the macro?).
Any way the data should end up in excel that way or another…

Thanks
Rafael

Rafaelz (BOB member since 2007-10-28)

system · March 26, 2009, 7:17am

You can write a small VB code snippet in the document_afterrefresh event to save the report data into Excel
For eg

Private Sub Document_AfterRefresh()

ThisDocument.SaveAs "C:\BOdocs\" &amp; ThisDocument.Name &amp; "-" &amp; Now() &amp; ".xls"


End Sub

The above code will save the document into Excel format and keep a history for each run (date suffixed to XLS file)
And use a windows batch file to run the BO macro report at a scheduled time and exit the application automatically

.

haider (BOB member since 2005-07-18)

system · March 26, 2009, 11:42am

Hi
Thanks, Will try to do it that way…
Rafael

Rafaelz (BOB member since 2007-10-28)

system · March 26, 2009, 8:10pm

Other techniques, including automation, described here … Using BusObj Reporter/DeskI as data source for Excel/Access

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · March 29, 2009, 8:53am

Hi Dwayne
Thank you very much. upon skimming the file it looks very helpful.
I’ll get right to using and implementing the information and see if i have further questions.
Thanks again for your help
Rafael

Rafaelz (BOB member since 2007-10-28)