SAP Set Variable with Array of Dates

[former_member1121432]

Hi,

I'm a complete newbie when it comes to SAP BI VBA.

I have a report that needs daily refreshes and the date range in prompts needs to be updated when new month comes around. For two weeks of a new month, the range needs to be Previous Month - Current Month, then it can be Current Month from and to

.

I found multiple solutions to when the prompt update is a single input, however in my case it's a range From - To.

I have tried both to filter by text MAY 2022 - MAY 2022 (this is how it shows in the Filter Information in the side pane) and by range like 05.2022 - 05.2022, but nothing works.

Below the code I have come up with - could you please support with how the function should be structured so that the array works?

Thanks!

Sub UpdateData()
Dim SetPeriod As Long
Dim RefPeriod As String

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

'RefPeriod = UCase(Range("r_FilterFrom").Value) & " - " & UCase(Range("r_FilterTo").Value) 'returns MAY 2022 - MAY 2022
RefPeriod = Format(Date - 14, "MM.yyyy") & " - " & Format(Date, "MM.yyyy") 'returns 05.2022 - 05.2022

SetPeriod = Application.Run("SAPSetVariable", "[0CALMONTH_C_C_I_M_001]", RefPeriod, "INPUT_STRING_AS_ARRAY", "DS_1")

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

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

End Sub

[AlexGourdet]

Thank you for visiting SAP Community to get answers to your questions.

Since you're asking a question here for the first time, I'd like to recommend you with the following steps so you can get the most out of your community membership:

• Please see: https://community.sap.com/resources/questions-and-answers as this resource page provides tips for preparing questions that draw responses from our members
• Consider to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html. as that will help you when submitting questions to the community.

I also recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,
-Alex