A new feature of Analysis for 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Dim R3 As Object 'Connection Object Set R3 = CreateObject("SAP.Functions") With R3.Connection .system = "ABC" 'system .client = "001" 'client .user = "reyemsaibot" 'user .Password = "4711" 'password .Language = "EN" 'language .systemnumber = "00" 'systemnumber .hostname = "172.23.13.70" 'hostname End With 'Test Connection If R3.Connection.logon(0, True) <> True Then Exit Function End If |
In Analysis for 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:
1 2 3 4 |
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.
These posts might also be interesting:
author.
I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
Write a comment