Analysis (AO) VBA variable update

[Former Member]

Hi,

I have multiple workbooks with Multiple data sources in each of them that need to be updated on a monthly basis.

The majority of the prompts stay the same but need to update the period for all data sources as the months go by.

Below a view of my Prompts screen with technical names included.

I would like to open the workbooks with a VBA code and then automatically let the code change the period, then refresh the data and save the workbook.

The opening part and saving part is easy but for some reason I'm not getting the variables to change with the code.

Below the code I've been playing with. Would really appreciate if someone can help me out.

Public Sub UpdateData()
Dim SetPeriod As Long

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

SetPeriod = Application.Run("SAPSetVariable", "[0P_FISCPER3]Posting Period", 6, "DS_1")

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

SetPeriod = Application.Run("SAPExecuteCommand", "RefreshData", "DS_1")

End Sub

Thanks in advance.

[Former Member]

Thanks Tobias,

I have changed it as per below and the code works perfectly fine now.

SetPeriod = Application.Run("SAPSetVariable", "0P_FISCYEAR", "2018, "INPUT_STRING", "DS_1")
SetPeriod = Application.Run("SAPSetVariable", "0P_FISCPER3", "6, "INPUT_STRING", "DS_1")

Is there a method to update the variable without having to "refresh" the data before hand, as that would basically mean running the report twice.

Thanks

[reyemsaibot]

Hi,

you need to refresh otherwise the return code of the API is 0 and it doesn't work. Have a look into the article from Martin

https://blogs.sap.com/2015/09/09/best-practices-for-vba-in-sap-bi-analysis-for-ms-excel/

You can also look at this post:

https://blogs.sap.com/2016/03/21/set-variables-before-first-refresh-using-callbacks-analysis-office-...