How can I get data from BO using VBA?

system · July 2, 2003, 2:38pm

Hi everybody,

I am new here and with BusinessObjects Programming. I was asked to get data from BO by using VBA in Excel. Before I tried use Designer technology, but I failed because I am not allowed to use the Designer Library in BusinessObjects as a normal user.

Should I use the ADO or ODBC to make the connection between excel and BO? if yes, what I need to know: DSN, UID, Password,…

Or is there a easier way?

If something is not clear, please ask me freely.

thanks in advanced.

Jimao

Jimao (BOB member since 2003-07-02)

system · July 2, 2003, 2:54pm

The first question should be to find out why the reporting need can’t be met with Business Objects itself. Why is the data needed in Excel? Yes, it is a battle you may not win (depending on your political environment), but it’s worth asking.

The answer to your question, however, is that there is a much easier way. First, create a reference to the Business Objects 5.1 Object Library in your VBA project (Tools, References). Then this sample code will get you close.

Sub GetData()
    
    Dim BOApp As busobj.Application
    Dim Doc As busobj.Document
    Dim DataProv As busobj.DataProvider
    Dim i As Long, j As Long

    Set BOApp = New busobj.Application
    BOApp.Visible = False
    Call BOApp.LoginAs
    Set Doc = BOApp.Documents.Open
    Set DataProv = Doc.DataProviders(1)

    For i = 1 To DataProv.Columns(1).Count
        For j = 1 To DataProv.Columns.Count
            ActiveSheet.Cells(i, j) = DataProv.Columns(j).Item(i)
        Next j
    Next i

    BOApp.Quit
    Set BOApp = Nothing
    
End Sub

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

system · July 2, 2003, 5:16pm

Hello Jimao,

Business Objects has a tool for doing this called BusinessQuery. Based on the usual development costs, it may be less expensive to purchase licenses to do this instead of programming. It works well and has a decent feature set. It is an Excel add-in that uses the Business Objects query panel to retrieve data.

If you are looking for something simple for just you to use, what Dwayne has offered is the way to go.

I hope this helps,
Gary

Gary Andrusiek (BOB member since 2003-04-22)

system · July 3, 2003, 9:37am

Hello Dwayne and Gary,

Thanks, the code Dwayne gave to me is working very well. But I would like to ask you a further question. Can I choose the universe name and the specific fields by using SQL language in the code instead of the report I have already created for?

What I did so far is I work on a project which collects data from different databases to create reports in Excel. I need the data from one database which I only get access by BO. so I try to get the current data automatically from the universe in BO instead of the manual data entry before.

thank you both for your useful help again!

Jimao

Jimao (BOB member since 2003-07-02)

system · July 3, 2003, 3:24pm

Is it possible? Yes, you can build a universe-based data provider on the fly. Look at the code here for some ideas. Then again, building it from Excel via VBA or natively in BusObj yields the same result, so I would think using native BusObj makes more sense. You could enhance my example by automating the logon, automating the BusObj refresh, and hiding that whole process so the user never sees anything.

All of this begs a much larger question. If this is going to be an ongoing type of reporting (data from different sources), it seems worth “doing it right” and creating an appropriate data warehouse, whether or not BusObj is your reporting tool. Just my two cents …

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

system · July 22, 2003, 8:48am

Thank you for your help, Dwayne. Now I got it.

Best wishes to you!

Jimao

Jimao (BOB member since 2003-07-02)

system · July 22, 2003, 11:47am

Sub GetData()
    
    Dim BOApp As busobj.Application
    Dim Doc As busobj.Document
    Dim DataProv As busobj.DataProvider
    Dim i As Long, j As Long

    Set BOApp = New busobj.Application
    BOApp.Visible = False
    Call BOApp.LoginAs
    Set Doc = BOApp.Documents.Open
    Set DataProv = Doc.DataProviders(1)

    For i = 1 To DataProv.Columns(1).Count
        For j = 1 To DataProv.Columns.Count
            ActiveSheet.Cells(i, j) = DataProv.Columns(j).Item(i)
        Next j
    Next i

    BOApp.Quit
    Set BOApp = Nothing
    
End Sub

Is there a way to amend this code so that a “named” business object document is opened?

So change this line;
Set Doc = BOApp.Documents.Open
to;
Set Doc = C\Bus Obj\Myfilename.rep

'code to open the file above…

jimboy (BOB member since 2003-07-22)

system · July 22, 2003, 1:51pm

The filename can be passed as the first argument to the Open command as follows:

Set Doc = BoApp.Documents.Open("C:\Bus Obj\Myfilename.rep")

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

system · July 22, 2003, 2:03pm

Thanks, easy when you know how.

jimboy (BOB member since 2003-07-22)

system · July 22, 2003, 2:06pm

True, but that F1 key is a great teacher! You may want to look at the full syntax, because there are other options (in addition to filename) that you may find useful.

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

system · June 17, 2004, 10:22am

Hi Dwayne,

Would it be possible for you to advise me how to code the login and password part to this please.

Many thanks
Lee

goat (BOB member since 2004-06-17)

system · June 17, 2004, 10:26am

This works for me;

    Dim BOApp As busobj.Application
    Dim Doc As busobj.Document
    Dim DataProv As busobj.DataProvider
    Dim PctDone As Single
    
    Set BOApp = New busobj.Application

    'BOApp.Visible = True
BOApp.LoginAs "Your login name here", "your password here", False

jimboy (BOB member since 2003-07-22)

system · June 18, 2004, 3:53pm

Hi,

Sorry to be a pain.

On the code that Dwayne has provided can someone please explain the following part for me as I am having problems and am unable to understand how this part works.

For i = 10 To DataProv.Columns(1).Count
For j = 1 To DataProv.Columns.Count
ActiveSheet.Cells(i, j) = DataProv.Columns(j).Item(i)
Next j
Next i

Also, for some of my reports there is a prompt on refresh which needs to be populated, I have noticed the automatic refresh part in the documents.open class but I cannot think of how to populate this prompt with a variable.

Any ideas ? any help would be much appreciated.

Many thanks
Lee

goat (BOB member since 2004-06-17)

system · June 20, 2004, 3:23pm

The code below loops through each row and column of the data provider and places each value in the cells (starting at A1) of the active Excel worksheet.

For i = 1 To DataProv.Columns(1).Count
    For j = 1 To DataProv.Columns.Count
        ActiveSheet.Cells(i, j) = DataProv.Columns(j).Item(i)
    Next j
Next i

To preset a prompt and refresh without seeing the prompt, insert the following between the Set Doc and Set DataProv lines:

Doc.Variables("Your Prompt Text").Value = "Your Prompt Value"
Application.Interactive = False
Doc.Refresh
Application.Interactive = True

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

system · June 25, 2004, 12:33pm

Hi,

Ok, i have 2 last questions on this and I’ll leave you alone

The line of code for the prompt does not seem to work for me, I was hoping you could detail exactly what is needed in the code so I can ensure everything is correct.

I have decided not to pull the data directly into excel but to run the reports, save them then open them as txt and take the data via VBA as pulling the data directly only gets the data from the data providers and on the reports are sum formulas which I need.

My question is what code do I need to use to change worksheet tabs in business objects so I can save each one individually ?

Also for some reports I need to export rather than just saveas and I have been unable to use the code correctly for this. Please could u provide an example.

I have looked on the internet for help with this but I cannot find any pages with advise other than this one and the help pages for BO dont seem to provide much help with the busobj objects and classes so if you know any helpful URLs they would be much appreciated.

Many thanks
Lee

goat (BOB member since 2004-06-17)

system · June 25, 2004, 12:45pm

Sorry, regarding the above, the prompt code does appear to add the specified data to the prompt but the prompt still appears. Is there any way to stop this appearing ?

goat (BOB member since 2004-06-17)

system · June 25, 2004, 4:06pm

the interactive line should supress it, make sure it is in the right place…

jimboy (BOB member since 2003-07-22)

system · June 25, 2004, 9:03pm

I can’t “detail exactly what is needed in the code” unless you tell me what “does not seem to work for me” means.

The following exports each report tab as a tab-delimited text file.

Dim Rpt As Report
For Each Rpt In ThisDocument.Reports
    Call Rpt.ExportAsText(ThisDocument.Path &amp; "\" &amp; Rpt.Name)
Next Rpt

In addition to ExportAsText as used above, there are ExportAsHtml, ExportAsPDF, and ExportAsRtf methods available.

The SDK documentation is about 500 pages long and lists every event, object, property, and method available.

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

system · June 25, 2004, 9:06pm

As jimboy pointed out, suppressing the prompt is the exact purpose of the Application.Interactive lines in the code provided in this post.

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

system · November 19, 2011, 9:44am

hi sorry to hijack this thread but i would like to ask how do you create a reference to business object in vba? i can’t seem to find the business objects 5.1 objects library. We are using Sap Business Object XI 3 i think.

btw, what is business object infoview? how is it different?

mr.X (BOB member since 2011-11-17)