Skip to Content
avatar image
Former Member

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

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 08 at 03:08 AM

    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

    Add comment
    10|10000 characters needed characters exceeded