Skip to Content
0

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

Feb 06 at 12:41 PM

132

avatar image
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
source.jpg (22.3 kB)
prompts.jpg (71.6 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Regan MacDonald Feb 08 at 03:08 AM
0

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

Show 6 Share
10 |10000 characters needed characters left characters exceeded
Former Member

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

0

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

Before :

After :

capture1.png (20.0 kB)
capture2.png (24.3 kB)
0
Former Member

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
technical-names.jpg (162.8 kB)
0

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?

0
Former Member
Regan MacDonald

Hi Regan,

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

KR Mert

0

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

0