I am trying to update the prompts in Analysis – which I do currently manually every month – for Excel; however, despite I am getting the value from technical name (which I guess is correct though), I can’t change it with the code bellow:

Public currentDateRange As String
Public expectedDateRange As String

Sub GetInfoFROMVariables()
Dim msg As String

currentDateRange = Application.Run(“SAPGetVariable”, “DS_1”, “ZCALMDEF”)

‘****Here the MsgBox currentDateRange retrieves the current value for ZCALMDEF, which is “2024/06 – 2024/11”.
MsgBox currentDateRange

expectedDateRange = GetNextMonthYear() & ” – ” & GetFutureMonthYear(6)
MsgBox expectedDateRange

End Sub

Function GetNextMonthYear() As String
Dim startMonth As Date
startMonth = DateAdd(“m”, 1, Date)
GetNextMonthYear = Format(startMonth, “yyyy/mm”)
End Function
Function GetFutureMonthYear(monthsAhead As Integer) As String
Dim futureDate As Date
futureDate = DateAdd(“m”, monthsAhead, Date)
GetFutureMonthYear = Format(futureDate, “yyyy/mm”)
End Function

Sub AtualizarPROMPTAnalysis()
Call GetInfoFROMVariables

If currentDateRange <> expectedDateRange Then

Application.ScreenUpdating = False

‘*****Here it does not work, neither the ARRAY or the expectedDateRange commands.
‘Application.Run “SAPSetVariable”, “DS_1”, “ZCALMDEF”, Array(GetNextMonthYear(), GetFutureMonthYear(7))
Application.Run “SAPSetVariable”, “DS_1”, “ZCALMDEF”, expectedDateRange

Application.ScreenUpdating = False

RefreshAll = Application.Run(“SAPExecuteCommand”, “Refresh”)

Application.ScreenUpdating = True

‘Here it shows normally what is expected “2024/07 – 2024/12”
MsgBox expectedDateRange

End If

‘
End Sub

Technical name is checked, also I know it is ok because I retrieved this value with MsgBox currentDateValue normally.

Thank you very much for your kindly attention and sharing your knowledge.

​I am trying to update the prompts in Analysis – which I do currently manually every month – for Excel; however, despite I am getting the value from technical name (which I guess is correct though), I can’t change it with the code bellow: Public currentDateRange As String
Public expectedDateRange As String

Sub GetInfoFROMVariables()
Dim msg As String

currentDateRange = Application.Run(“SAPGetVariable”, “DS_1”, “ZCALMDEF”)

‘****Here the MsgBox currentDateRange retrieves the current value for ZCALMDEF, which is “2024/06 – 2024/11”.
MsgBox currentDateRange

expectedDateRange = GetNextMonthYear() & ” – ” & GetFutureMonthYear(6)
MsgBox expectedDateRange

End Sub

Function GetNextMonthYear() As String
Dim startMonth As Date
startMonth = DateAdd(“m”, 1, Date)
GetNextMonthYear = Format(startMonth, “yyyy/mm”)
End Function
Function GetFutureMonthYear(monthsAhead As Integer) As String
Dim futureDate As Date
futureDate = DateAdd(“m”, monthsAhead, Date)
GetFutureMonthYear = Format(futureDate, “yyyy/mm”)
End Function

Sub AtualizarPROMPTAnalysis()
Call GetInfoFROMVariables

If currentDateRange <> expectedDateRange Then

Application.ScreenUpdating = False

‘*****Here it does not work, neither the ARRAY or the expectedDateRange commands.
‘Application.Run “SAPSetVariable”, “DS_1”, “ZCALMDEF”, Array(GetNextMonthYear(), GetFutureMonthYear(7))
Application.Run “SAPSetVariable”, “DS_1”, “ZCALMDEF”, expectedDateRange

Application.ScreenUpdating = False

RefreshAll = Application.Run(“SAPExecuteCommand”, “Refresh”)

Application.ScreenUpdating = True

‘Here it shows normally what is expected “2024/07 – 2024/12”
MsgBox expectedDateRange

End If

‘
End Sub Technical name is checked, also I know it is ok because I retrieved this value with MsgBox currentDateValue normally.Thank you very much for your kindly attention and sharing your knowledge.  Read More

​