Integration with Visual Basic for Applications 

You can also connect the Business Explorer (BEx) with your own VBA programs (Visual Basic for Applications). The BEx add-in can trigger your own VBA routines or you can control BEx add-in functions from your own VBA routines.

Controlling from the BEx Add-in

When you insert a query into the workbook, the following VBA routines are automatically generated in the workbook:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

End Sub

This routine is always called if the BEx Analyzer has filled the results area of the query. This routine has the parameters Query ID and the Results Area as the Excel range. You can use this routine to trigger your own processes after you refresh the query.

You can display the ID of a query by choosing Properties ® Information in the context menu.

See also:

Query Properties

You want the current date and the time to be entered in the cells directly above the upper left area of the result area every time the query is refreshed.

You supplement the VBA routine as follows:

Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

If queryID = "SAPBEXq0003" Then

With resultArea.Resize(1, 1)

.Offset(-1, 0) = "Last calculated at:"

.Offset(-1, 1) = Now

End With

End If

End Sub

" SAPBEXq0003 " is the query ID here. You can display the query ID under the query properties.

If you require the same behavior for all queries in the workbook then the ‘If’ statement is omitted.

Controlling the BEx Add-in

You can call up various VBA routines of the BEx add-in, with which you control the Business Explorer Analyzer functions. The following call-ups are available:

Function SAPBEXgetWorkbookID(wbName As String) As String

Determining the ID, under which a workbook is saved in the InfoCatalog.

The workbook must be opened in Excel; its current name in the collection Application. Workbooks must be entered in the parameter wbName . You will see that the technical name of the workbook generally differs from the title in the window.

The function provides an empty string if the workbook is not found in the collection Application Workbooks , or if it is established that it has not yet been saved in the InfoCatalog.

Function SAPBEXreadWorkbook(wbID As String) As String

Reading a workbook from the InfoCatalog.

The ID for the workbook in the InfoCatalog must be entered in the parameter wbID .

You can use the function SAPBEXgetWorkbookID to determine a workbook ID. However, you will see that an ID is generally only valid in a certain InfoCatalog.

If the reading process was successful, the function shows the Excel name of the open workbook as a result, or the empty string.

If there is no connection to an OLAP server, the function starts a logon dialog.

Function SAPBEXsaveWorkbook(wbName As String) As Integer

Saving a workbook in the InfoCatalog

In the parameter wbname, you must enter the Excel name of the workbook you want to save. The name must be derived from the InfoCatalog and is saved there "as a new version".

If there is no connection to an OLAP server, the function starts a logon dialog.

Function SAPBEXgetErrorText() As String

This return code can be interpreted as an error code for all API functions, that provide a result of type integer:

-1 Context error, this means that the function cannot be used on the cell in question

0 The function was executed without errors

> 0 An error occurred in the function

In the last case, the text on the error can then be requested with SAPBEXgetErrorText (until the next call-up of an API function).

Function SAPBEXsetFilterValue(intValue As String, Optional hierValue As String, Optional atCell As Range) As Integer

Filtering with cell atCell (if this parameter is missing, then the active cell is taken as the basis)

The internal filter value must be entered in the parameter intValue. Please note that this value can be displayed differently from the external value displayed in the filter cell. You can either use SAPBEXgetFilterValue or carry out the filter process one time, manually, with the log book function switched on in order to determine the internal value for a known external value. The internal filter value is noted in the log.

Enter the parameter hierValue when dealing with a node of a certain characteristic hierarchy with the filter value.

" intValue = <blank> " means "do not filter anymore"

If there is no connection to a BW server, the function starts a logon dialog.

The function produces a context error if atCell is not a filter cell.

Function SAPBEXgetFilterValue(intValue As String, hierValue As String, Optional atCell As Range) As Integer

Determining the internal filter value (and, where necessary, the characteristic hierarchy used) with cell atCell (if this parameter is missing, then the active cell is taken as the basis).

The result is displayed in the parameters intValue and hierValue , which you subsequently have to transfer to " byRef ". As no byRef parameter transfer is supported when calling up this function with the Excel method "Run", both of the values can alternatively be queried using the help functions SAPBEXgetFilterValue_intValue and SAPBEXgetFilterValue_hierValue after the call-up of SAPBEXgetFilterValue, for example.:

if run("SAPBEX.XLA!SAPBEXgetFilterValue", myValue, myHier) = 0 then

myValue = run("SAPBEX.XLA!SAPBEXgetFilterValue_intValue")

endif

The function produces a context error if atCell is not a filter cell.

Function SAPBEXsetDrillState(newState As Integer, Optional atCell As Range) As Integer

Change drilldown status with cell atCell (if this parameter is missing, then the active cell is taken as the basis).

" newState = 0 " means "do not drilldown anymore"

" newState = 1 " means "drilldown vertically""

" newState = 2 " means "drilldown horizontally"

If there is no connection to a BW server, the function starts a logon dialog.

The function produces a context error if atCell is not a filter cell.

Function SAPBEXgetDrillState(currentState As Integer, Optional atCell As Range) As Integer

Determining the drilldown status with cell atCell (if this parameter is missing, then the active cell is taken as the basis).

The result is returned in the parameter currentState , that must subsequently be transferred to " byRef ". As no byRef parameter transfer is supported when calling up this function with the Excel method "Run", the value can, alternatively, be queried after the call-up of SAPBEXgetDrillState using the help function SAPBEXgetDrillState_currentState (see SAPBEXgetFilterValue).

The function produces a context error if atCell is not a filter cell.

Function SAPBEXrefresh(allQueries As Boolean, Optional atCell As Range) As Integer

allQueries = FALSE: Refreshing the query with cell atCell (if this parameter is missing, then the active cell is taken as the basis). In this variant, the function produces a context error, if atCell does not belong to a results area or is a filter cell.

allQueries = TRUE: Refreshing all queries in the active workbook.

If there is no connection to a BW server, the function starts a logon dialog. - provided no queries have to be refreshed.

Sub SAPBEXpauseOn()

Stopping the process run in the add-in

You switch on Stop Automatic Refresh in the BEx Analyzer when you call this function. This means the changes you make are noted with some of the navigation operations, but the query is not immediately refreshed. This applies above all to the API call SAPBEXsetFilterState .

Sub SAPBEXpauseOff()

Continuing the process run in the add-in

All changes that are noted since the process run in the add-in (invoked either by user interaction or by calling up SAPBEXpauseOn ) are now fetched by refreshing the queries in question.

If there is no connection to a BW server, the function starts a logon dialog – as long as no queries have to be refreshed.

Function SAPBEXfireCommand(fCode As String, Optional atCell As Range) As Integer

Executing an OLAP function with cell atCell (if this parameter is missing, then the active cell is taken as the basis).

The cell in question must belong to the results area of a query and this query must have been refreshed in the current session.

You must enter the code of the OLAP function. In order to determine this code you carry out the OLAP function once manually with the logging function switched on. The OLAP function code is noted in the log.

To be sure that you can use the OLAP function on specified cell, always call up SAPBEXcheckContext.

The function produces a context error if atCell does not belong to a results area.

Function SAPBEXcheckContext(fCode As String, Optional atCell As Range) As Integer

Checks whether a given OLAP function can be used with cell atCell (if the parameter atCell is missing, then the active cell is taken as the basis).

If there is no connection to a BW server, the function starts a logon dialog.

This function produces a context error if the check produces negative results, if the active cell does not belong to a results area, or if the query has not been refreshed during the current session.

Function SAPBEXgetFilterValue(intValue As String, hierValue As String, Optional atCell As Range) As Integer

Jumps to a view, which you have to define in the workbook. The parameter jumpType must always by set to "v"("v" for view, other jump types are in development). The name of the view is displayed in the parameter jumpTarget. If there is no connection to a BW server, the function starts a logon dialog.

There is a context error if the active cell does not belong to a results area.