Skip to Content
1
Former Member
Apr 30, 2014 at 01:29 PM

Set filter on measures during workbook start

759 Views

Hi,

I am trying to set a filter on the measures of a plan query. Depending on the current calendar month either two or one key figures should be hidden. I would like to compare the current system date to a threshold value each time I start the workbook.

The code (bold part of the sub) I created for this works fine when I am running it through pressing a button but when I put the code in the Workbook_SAP_Initialize() sub nothing happens.

The sub also includes other code, I am not sure whether it has something to do with the problem (even though I also tried the filter part without the rest of the sub and it did not work either). I also tried the regular Workbook_Open sub that Excel provides but that didn't work.

Does anybody have an idea?

Public Sub Workbook_SAP_Initialize()

' register callback
If ThisWorkbook.Worksheets("Sheet2").Range(HC_flag) = "X" Then
Call Application.Run("SAPExecuteCommand", "RegisterCallback", "AfterRedisplay", "Callback_AfterRedisplay")
Call Application.Run("SAPAddMessage", "Callback registered successfully")
Call AO_TRACK_CHANGES.clearMapTable
End If

Call Application.Run("SAPExecuteCommand", "RegisterCallback", "BeforePlanDataSave", "Callback_BeforePlanDataSave")

Dim sysDate As Date ' Aktuelles Systemdatum
Dim sysMonth As Long ' Monat des aktuellen Systemdatum
Dim lResult As Integer

sysDate = Date
sysMonth = Month(sysDate)

If sysMonth > "6" Then
lResult = Application.Run("SAPSetFilter", "DS_1", "4XATOD246ZMLXOQKXL1IPVMSF", "!4XATO9FJ8NDK51JY6DXPYY8M7")
lResult = Application.Run("SAPSetFilter", "DS_2", "4X2N5TODT2KU9C774KF2UI9EN", "!4X1HRMZ1M9MBFBIS5WO86HTLR")
ElseIf sysMonth <= "6" Then
lResult = Application.Run("SAPSetFilter", "DS_1", "4XATOD246ZMLXOQKXL1IPVMSF", "!4XEAX4Z6W4JA6CCSUYXOD2RRZ;!4XATO97UPORUMF0I0JVDOW9WF")
lResult = Application.Run("SAPSetFilter", "DS_2", "4X2N5TODT2KU9C774KF2UI9EN", "!4X1KMM9L3BCQ8CDJUTYW2QMFZ;!4X1HRN6Q5880XY28BQQKGJSBJ")
End If

End Sub

Thanks, Jan