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.

[reyemsaibot · Accepted solution]

Hi,

when I look into the SAPSetVariable command it looks like this

ret = Application.Run("SAPSetVariable", "0COUNTRY", "DE", "INPUT_STRING", "DS_1")

When I now look at your code, I miss "INPUT_STRING" or "KEY" or "TEXT" etc. and your variable also is not correct. Please fix this and look into the user guide then it should work.

best regards,

Tobias

[former_member1356063]

Hi

I have tried to Update a flexbook lige below, but something are missing to be able to insert data into the fields below, but I’m stuck right now.

The first field should contain

27

#

Today’s date in the to date fields

but the below code don’t do it so what is missing?

best regards

Martin

Public Sub UpdateData1()
Dim SetPeriod As Long

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

SetPeriod = Application.Run("SAPSetVariable", "4.500-2 Operations Flex Query|Stockyard Hierarchy[VAR_PSD_FA01]", "Denmark", "INPUT_STRING", "DS_2")
SetPeriod = Application.Run("SAPSetVariable", "VNC1MR_STOCKYARD", "+#(Text Node)", "INPUT_STRING", "DS_2")

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

SetPeriod = Application.Run("SAPExecuteCommand", "RefreshDataSource", "DS_2")

[former_member186338]

Sorry, but this is not an "answer" to the question and reply in the old question is a bad idea in general...

Please ask a new question.

[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-...