API Function SAPOpenWorkbook

[reyemsaibot]

Hi,

I just want to explain the new Analysis Office function SAPOpenWorkbook. The original post was on my blog. I hope this little instructions help someone.

A new feature of Analysis Office 2.3 is a macro called SAPOpenWorkbook. Some of you maybe know the old sapbex.xla!SAPBExReadWorkbook or the BExAnalyzer.xla!runWorkbook command.

The user guide explanation for this macro is:

You can use this API method to open an Analysis workbook. The workbook that should be opened can be stored on a SAP NetWeaver server or on a BI platform.

The workbook is opened in the same Microsoft Excel instance. After the command execution, the opened workbook is active.

As you know the sapbex.xla!SAPBExReadWorkbook respectively BExAnalyzer.xla!runWorkbook command needed either an established connection from the addin or you created your own connection with the following code:

Dim R32 As Object 'Connection Object

Set R32 = CreateObject("SAP.Functions")

With R32.Connection

    .system = "ABC" 'system

    .client = "001" 'client

    .user = "reyemsaibot" 'user

    .Password = "4711" 'password

    .Language = "EN" 'language

    .systemnumber = "00" 'systemnumber

    .hostname = "www.reyemsaibot.com" 'hostname

End With

'Test Connection

If R32.Connection.logon(0, True) <> True Then

Exit Function

End If

In Analysis Office the macro SAPOpenWorkbook use the connection of an existing DataSource. So you can't define your own connection with a super user or something like that. Here is an example code:

Sub OpenWorkbookViaAPI()

Dim lresult As Long

lresult = Application.Run("SAPOpenWorkbook", "DEMO_5", "DS_1", "ZCOUNTRY_VAR_02", "AT", "0I_FPER", "001.2011 - 004.2011")

End Sub

The name Demo_5 is the technical name of the workbook from a SAP NetWeaver server. If you want to use a document, which is stored on a BI Platform, you need the CUID.

The connection parameters will be used from the DataSource DS_1. This parameter is mandatory if you have several connections in a workbook. If you have only one connection, it is optional. The target workbook will be opened from the referenced connection of DS_1.

The variable parameter for ZCOUNTRY_VAR_02 is optional, but if you have a mandatory variable you have to assign a value, like the 0I_FPER variable.

If you want to refresh your workbook on opening, you have to set the parameter “Refresh Workbook on Opening”. You also need an established connection or you see the logon popup.

New is the feature that you can predefine your variable value for the new workbook. This is nice but it is still not the same as in BEx Analyzer, because you have to use the connection from a existing DataSource. I hope SAP will provide us more in the future.