Workbook having queries on different BW servers

[Former Member]

Hello,

I need to implement a workbook, in where the queries are from different BW machines.

Ofcourse the workbook is on one of the machines.

So my question is that is it possible to use VB macro which does the following:

- Refresh query1 which is on the same machine as the workbook

- Refresh query2 which is on a different machine (would require disconnecting current machine, automatic login to other machine and refreshing the query).

Regards

Pranay Dave

[Former Member]

The problem will be, even if you can handle the sequential log on, that you can only navigate in queries on a server where you are actually logged on.

Therefore, I am not sure, if it is really usefull to create a workbook containing queries on different systems.

To do this, you might create a remote cube that accesses data on another BW system.

[Former Member]

Hello Maik,

Thanks for your response.

How do you handle the sequential log on.

Regards

Pranay

[tom_francis2]

For loggin on check note: 0492561. It explains how to logon using vba.

Afterwards use the following VBA code:

Run "sapbex.xla!SAPBEXrefresh", True

With a little bit of logic by yourself you must be able to make it work.

kr,

Tom

[Former Member]

Hello Tom,

Points to you for OSS note.

Next questions:

- Is there any function other than SAPBEXrefresh by which you can refresh(or execute) the query. Because in SAPBEXrefresh, I need to be on the worksheet of the query which has to be executed

- How do I logout of a BW system using VB code

Pranay

[Former Member]

Logging out via VBA:

The code from note 492561 logs out of BW in the first step: "logonToBW=False", sets the credentials for the new login, and "logonToBW=True" as the last step.

If the user names/passwords are different for the 2 systems, you should consider 2 routines with the approp. credentials.

This is recommended over an IF statement for ease of maintenence due to expiring user names and passwords now being hard coded.

[Former Member]

Praney,

You DO NOT need to be on the worksheet that is being refreshed in order to use SAPBEXrefresh. The format for this function is:

SAPBEXrefresh(all Queries as Boolean, [atCell as Range])

The "atCell" is optional. So, you COULD ignore it. In which case the function uses the active cell to tell the Analyzer which query to refresh. But, you COULD choose to use it. As long as the atCell range is defined with worksheet as well as cell address, it will work fine. By the way, I suggest setting the code up using the codename for the worksheet instead of the common name; that way, if the user changes the name on the tab of the worksheet, your VB coding will continue to work just fine. If the user is knowledgable enough to change codename of a worksheet then they should know better than to expect your code to still work.

Just to prove a point ... I have a workbook where one of the queries is on a hidden worksheet. I first implemented it turning off screen updating so the user did not see the hidden query and unhiding the sheet. Later, with some prodding, I tested it leaving the hidden sheet hidden. (If it is hidden, it can never be active.) And it runs just fine.

Another minor point ... I doubt that you need to log off of one server before logging on to another. I have not tried it. But, every time that I use the automated log in, it logs me off first.

- Pete