Skip to Content
Author's profile photo Tobias Meyer

API Function SAPOpenWorkbook


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 = “” ‘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.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Joost Peeters
      Joost Peeters

      Thanks for trying that out for us, Tobias!

      Best regards,


      Author's profile photo Michal Prusak
      Michal Prusak


      I would like to open one saved Workbook (called "AMSTEST") with VBA from MS Excel. This WB was saved on NetWeaver Platfor server -> according to docu of the analysis for MS Office should be technical name of workbook enough.

      Sub wbOpen()
          Dim lResult As Long
          lResult = Application.Run("SAPOpenWorkbook", "AMSTEST", "DS_1")
      End Sub


      I got this error message


      Analysis Add-in was installed succesfully und works for another issues fine.


      If I run this VBA code, add-in is already loaded -> I assume it should work fine, but does not.

      Can someone give me any help to solve this problem or explain what I make false?


      Thank you very much in advance.

      Michal P.

      Author's profile photo Juan Carlos Aljama
      Juan Carlos Aljama

      Hi Michal,


      Change the routine name to  "Sub SAPOpenWorkbook()" and try again.



      Author's profile photo Juan Carlos Aljama
      Juan Carlos Aljama

      If it does not work either, try to write the routine in “ThisWorkbook” Microsoft Excel Objects


      Author's profile photo Tobias Meyer
      Tobias Meyer
      Blog Post Author


      the last time I checked it with 2.4 it works. Which Analysis Office version do you use? The error message seems like the Analysis Office Addin is not available.


      Best regards,



      Author's profile photo Juan Carlos Aljama
      Juan Carlos Aljama

      Hello Tobias,


      Is there any way to set the value of a variable by linking a cell in Workbook?


      e.g.  "[DS_1]0COUNTRY=$C$3;0PRODUCT=P01;[DS_2]0COUNTRY=Germany"


      Thanks in advance


      Juan Carlos

      Author's profile photo Tobias Meyer
      Tobias Meyer
      Blog Post Author

      Hi Juan,


      normaly this should work as you described. But you may have to escape the cell with ' '

      [DS_1]0COUNTRY='" & Range("C3") & "';0PRODUCT=P01"


      Maybe you can test this?

      Author's profile photo Juan pablo Montero de Blas
      Juan pablo Montero de Blas

      Thank you Tobias. Works fine!


      Best regards,


      Juan Carlos