How to pass a date interval via VBA to SAP Business Objects?

[Former Member]

Hello Dear SAP Community,

I am trying to write some VBA code that works with SAP BO Analysis for MS Office (2.3.3.63237 MUI 1.2). My primary intention is to create a macro, which when clicked, should first refresh the data source(s) in the spreadsheet, and then update the data sources according to some instances of key figures in a table somewhere in the said spreadsheet (also below). Where I am stuck now is how to indicate a certain date interval. The following code does not work as I am doing a mistake either with the syntax or my approach is entirely wrong on the line where I try to pass the date interval.

How do I, for instance, change the sign from "=" to "[]" in this case? Is this a necessary step to pass down an interval, or is there another way? How do I know the 'technical names' of these fields on the Prompts window?

As you can tell, I am not professional in this area. I merely experimented with the VBA and SAP BO and wrote the below code . Any guidance on how to do this in a better way would be much appreciated!

How the Prompts window looks like: (I want to pass the date as an interval in this case, as below):

How the source table looks like:

Public Sub MyRefresh()
    Dim lResult As Long
    lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_32")  
    Call Application.Run("SAPSetRefreshBehaviour", "Off")
    Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")
    myBegDate = Worksheets("source").Range("AQ4").Value
    myEndDate = Worksheets("source").Range("AR4").Value

    lResult = Application.Run("SAPSetVariable", "Calendar Month/Year", myBegDate - myEndDate, "INPUT_STRING", "DS_32")
    lResult = Application.Run("SAPSetVariable", "APO Market", Worksheets("source").Range("AT4").Value, "INPUT_STRING", "DS_32")

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

[reganmacdonald · Accepted solution]

Hi Mert,

For the variable to update you will need to use the technical name for it, not the cosmetic name. i.e. not "Calendar Month/Year"

Also, for the date range, ideally build it into another field first and then use that field in your SAPSetVariable call.

MyDateRange = myBegDate & " - " & myEndDate

Application.Run("SAPSetVariable","<variable technical name>",MyDateRange,"INPUT_STRING","DS_32")

This page here gives some more info on how to make use of the syntax : https://blogs.sap.com/2017/02/03/analysis-for-office-variables-and-filters-via-vba/

Cheers,

Regan

[Former Member · Accepted solution]

Hi Regan,

Thank you! This did the trick. I had read your article before, but could not find the right syntax I'd need to formulate.

Regarding the names, the data sources are somehow updating correctly even with these cosmetic names above, that I am also seeing on the Prompts window. I had just experimented with it and it works.

I did not find where I could see the technical names of these fields though. Any hints on that?

Kind Regards

Mert

[reganmacdonald · Accepted solution]

When the prompts window comes up, click on the Display button in the bottom left and tick 'Show Technical Names'.

Before :

After :

[Former Member · Accepted solution]

Hello Regan,

Thanks for the hint. I looked into the Display button on the Prompts window and the below is what I am seeing:

Here, Show All, by the name implies, is showing all the key figures there are and Hide All is simply hiding everything. Hide Optional Prompts is simply doing the same as Hide All, as all key figures are apparently regarded as optional in our implementation. As seen above, there is no choice in this drilldown to show the technical names of the key figures, so maybe this is then the reason why the 'cosmetic names' are indeed working for me in this case.

Kind RegardsMert

[reganmacdonald · Accepted solution]

It looks like you don't have the option to show technical names, its usually the 4th and last option in that dropdown list.

What version of AO are you using?

[Former Member · Accepted solution]

Hi Regan,

The version I have is SAP BO Analysis for MS Office (2.3.3.63237 MUI 1.2).

KR Mert

[reganmacdonald · Accepted solution]

My screenshots for this thread are from 2.5 SP3. Specifically 2.5.300.76689