VBA Interface & More

[Former Member]

Hola,

I am using BW/BEx version 3.0B Patch 12 3020.12.637.

I have limited experience with BW/BEx.

I would like to know the following:

1) As Excel is used for the interface, is there a way to execute BW queries directly from VBA functions? I can refresh the queries embedded on a given Excel worksheet without a problem. I would like to know if there is another possibility.

2) Is there a way I can populate the query input dialog from VBA functions when a given query is refreshed? Some of the data input remains the same however some of it changes from run to run.

3) Is there a way to execute queries directly on the server as if it were a standard database management system?

4) Is there a way to simultaneously execute several queries that are embedded in different worksheets in a workbook? In one case there are twelve different queries. To populate the final report with appropriate data, typically only three are run. Currently, the queries are executed sequentially which requires human input to start the next query.

5) What is the most effective method of starting the BEx Analyzer from code. I notice that when I just start the xla located at "C:\Program Files\SAP\FrontEnd\Bw\sapbex.xla", the only BEx function available is refresh. Also, sometimes there are odd errors when starting BEx in this fashion. Is there a way to start the XLA / Add-In from VBA functions that is equivalent to a user clicking on the BEx icon?

Thank You In Advance

-V-

[Former Member · Accepted solution]

Hola Vir,

The answer to your questions is "si". Except, it is the French meaning of the word.

The easiest way to automate BW within Excel is to use the API functions included in the Add-In SAPBEX.xla.

You address these functions like you would any time that you want to run a macro that is in another workbook. Namely, you use the Excel function "Run" instead of the Visual Basic method "Call".

For example, to refresh a single query (let's say it is embedded on worksheet "Sheet1" and that cell A5 is one of the cells that is part of the query), the function call looks like this:

Run "sapbex.xla!SAPBEXrefresh", False, Sheets("Sheet1").Range("A5")

If, instead, you want to refresh all queries in the workbook, use:

Run "sapbex.xla!SAPBEXrefresh", True

There are two ways to populate the refresh variable screen. The way that is recommended is to define a range that contains the variables you want to fill, then pass it using the API function SAPBEXsetVariables. For example:

Dim varRng as Range

Sheets("Sheet1").Select

Range("A5").Select

Set varRng = Range("refresh_Mask") 'using a Named Range

Run "sapbex.xla!SAPBEXsetVariables", varRng

Note that the ActiveCell must be part of the embedded query that you want to refresh!

The only way that I know of to run queries directly on the server and then embed them in a workbook when they are finished is to use RRI (Report-Report Interface). There are probably other ways.

To run queries sequentially, you can use a combination of SAPBEXrefresh and SAPBEXsetVariables.

If you load the sapbex.xla programmatically as a workbook, you must also force it to run its auto-exec macro. For example:

Workbooks.Open(Filename:="C:\Program Files\SAP\FrontEnd\Bw\sapbex.xla").RunAutoMacros _

Which:=xlAutoOpen

If you load it as an Add-In, however, this is not required.

AddIns.Add Filename:="C:\Program Files\SAP\FrontEnd\Bw\sapbex.xla"

AddIns("SAP Business Explorer Analyzer").Installed = True

If you search this forum for SAPBEXrefresh and SAPBEXsetVariables, you will find more details. Or, post again if you have questions.

- Pete