VBA API for SAP BO Analysis for Excel

Question

I haven't found many articels on this yet on stackoverflow so i will elaborate a bit more by giving the necessary background info.

I'm using the SAP BO Analysis for Excel add-in through which i'm retrieving data in Excel from SAP BW.

The Analysis add-in comes with a GUI through which you can enter variables in the query (so called prompts).

It additionally has an VBA API so you can set the variables through VBA.

My big problem is that the setting of variables through VBA only seems to work when i have set atleast 1 prompt through the GUI :S

Below is my full VBA code. It's inspired on http://precisionstudio.net/uploads/3/0/5/0/3050843/sap_instructorhandbook.pdf & http://scn.sap.com/community/businessobjects-analysis-ms-office/blog/2011/06/26/can-you-use-vba-with-businessobjects-analysis-find-out-here

The Dims lResul, lResult & lResultt return 0, but when i manually fill in a value in the GUI prompt and re-run the VBA macro, it works (returns 1)... it fills in the other values. Very strange.

Has somebody encoutered same issues and know the way out?

Many thanks in advance

  Private Sub EnableAnalysisOffice()

    Dim addin As COMAddIn

    For Each addin In Application.COMAddIns

        If addin.progID = "SBOP.AdvancedAnalysis.Addin.1" Then

            If addin.Connect = False Then addin.Connect = True

        End If

    Next

End Sub


Private Sub Workbook_Open()

    Call EnableAnalysisOffice
    Call MyGetData

End Sub


Public Sub MyGetData()

    Dim Iret As Long

    Dim lResul As Long

    Dim lResult As Long

    Dim lResultt As Long

    Iret = Application.Run("SAPLogon", "DS_1", "200", "myUser", "myPassword")

    MsgBox (Iret)

    Call Application.Run("SAPSetRefreshBehaviour", "Off")
    Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")

    'Select data based on WBS

    'lResul = Application.Run("SAPSetVariable", "0S_WBSE", Worksheets("Prompt Values").Range("A2").Value, "INPUT_STRING", "DS_1")
    'MsgBox (lResul)

    'Select data based on Employees

    lResult = Application.Run("SAPSetVariable", "YEMPLSEL", Worksheets("Prompt Values").Range("A5").Value, "INPUT_STRING", "DS_1")
    MsgBox (lResult)

    'Document Date
    lResultt = Application.Run("SAPSetVariable", "YDOCUMENT_DATE", ">=01\-01\-2015", "INPUT_STRING", "DS_1")
    MsgBox (lResultt)

    Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "Off")

    Call Application.Run("SAPSetRefreshBehaviour", "On")

    'Call Application.Run("SAPExecuteCommand", "Refresh")

End Sub

Doesn't every Excel-Add in created by commercial software packages come with a documentation of how to use it and what its limits are (specially that you are using SAP)? — bonCodigo
After you run the GUI manually once, does the VBA always work until you quit Excel? If so, maybe some part of the add-in (library,etc) doesn't get loaded by calling addin.Connect = True — ChipsLetten
I have found a solution in the meantime, not really a nice one. I save the current prompts within the workbook & refresh with those prompts. After that, i allows me to modify the prompts. Just sharing in case somebody encounters the same problem... — user1186098
I recommend raising a ticket/incident about this with SAP, they can consider it and either fix it or take it in as an enhancement request for a future release. — Villager

user5856054 user5856054 · Accepted Answer · 2016-01-29T08:11:43

Maybe too late to answer... I read a article called "Best Practices for VBA in SAP BI Analysis for MS Excel". (I didn't link it since I don't know if it's allowed or not).

Basically, it said that we should refresh before using SAPSetVariable. I added following code before SAPSetVariable, and it worked for me.

lResult = Application.Run("SAPGetProperty", "IsDataSourceActive", "DS_1")

If lResult = False Then
    lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")
End If

VBA API for SAP BO Analysis for Excel

1 Answers