Calling Business Objects 4.1 from Excel VBA

[Former Member]

I'm not sure if any of you do this. We currently have Excel VBA that calls Deski reports, opens them, passes in variables, refreshes, and downloads the data into Excel.

There are many valid reasons for my users to do this, and they need to continue with this process once we move to 4.1 But Deski will no longer be there so we need to come up with a new way to do this. I could code just about everything except for the part that asks the user to save or open the file.

How can I get around this? Is there something I might be missing? They need to run their process on demand so scheduling a report won't work for us.

Thanks so much for any help you might be able to provide.

[christian_ah-soon]

I do not know that much VBA and I've never used it, but I think that it is possible to call REST WS from VBA.

If yes, then the REST WS can be used to connect to the SAP BI platform, get the WID, refresh it (and answer prompts).

Then perhaps the tricky point might be to programmatically get the data from the report and properly parse it in order to fill the spreadsheet.

~~cas

[Former Member]

Hi Anna and Christian,

Yes you´re abble to make HTTP calls from VBA. In addition to what Christian sugested, I would add that you take a look at

Regards,

Rogerio

[Former Member]

Yeah, the tricky part is definitely getting the data out of the report.

I'm trying the LiveOffice route right now, but I'm not sure if I'll be able to code the authentication part.

I know I can make HTTP calls from VBA, and I'm able to code just about everything until I get to the part where I need to save the data. The save or open dialogue is very difficult to get around, for security purposes I'm sure.

Thank you both, I will update if I ever get this figured out. 

[christian_ah-soon]

Hi Anna,

Official SDK for Web Intelligence in 4.x is REST Web Services. Using them, you can write some code that can be called and process the workflow you described.

http://service.sap.com/~sapidb/012002523100011674692015E/sbo41sp6_webi_restws_dg_en.zip

Another option is to use SAP Live Office, where your users can directly import Web Intelligence report element's data in the spreadsheet.

Regards

~~cas

[Former Member]

Is the SDK a Java thing? In have heard of this as a solution but I know nothing about Java and my boss isn't going to be too supportive of me having to learn new stuff for this.

LiveOffice has also been brought up and I'm going to suggest it at the next meeting about this, but I worry they won't be into this because they already use a number of plug ins and are paranoid about things messing up. Their data is super critical and we're barely allowed to touch anything.

Any other solutions? Or is that about all there is?

[christian_ah-soon]

No, this is an HTTP API.

You connect to the SAP BI 4 and SAP Web Intelligence servers using URL and passing arguments using XML or JSON.

You can tell your boss it's fun to learn

Regards

~~cas

[Former Member]

oh that's awesome! forgive my dumb questions - so this will allow me to not only pass in arguments and refresh, it will also allow me to automate the export process without the download dialogue getting in the way?

Thank you so much for all of your help.

[Former Member]

I tried clicking the link but it's asking me for authentication. Does this cost $?

[christian_ah-soon]

No it's free, but to make sure it is used only by registered users, it's hosted on another site (SMP I think) that requires your authentication.

~~cas