Skip to Content
avatar image
Former Member

SAPSetFilter


Hi,

I am trying to set the filters in my workbook using VBA code. I am using the code below, however, the SAPSetFilter call returns a result of 0 (i.e. fails). The technical name is correct and the value is valid. I can get the SAPSetVariable call to work correctly for the fields that are prompts. I have tried many searches but do not seem to be able to work it out.

Any help would be greatly appreciated.

Regards

Lachlan

dataSourceAlias = Application.Run("SAPGetCellInfo", Excel.ActiveWorkbook.Sheets("InventoryData").Cells(1, 1), "DATASOURCE")

lResult = Application.Run("SAPSetRefreshBehaviour", "Off")

lResult = Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")

lResult = Application.Run("SAPSetVariable", "Calendar Month/Year From", "01.2014", "INPUT_STRING", dataSourceAlias)

lResult = Application.Run("SAPSetVariable", "Calendar Month/Year TO", "08.2014", "INPUT_STRING", dataSourceAlias)

PlantSel = "1000"

lResult = Application.Run("SAPSetVariable", "Plant (Selection Options, Optional)", PlantSel, "INPUT_STRING", "DS_1")

'''''''''''''''''All above code works ok''''''''''''''

''''''''''''''''the line below returns a value of 0'''''''''''''''''''''

lResult = Application.Run("SAPSetFilter", dataSourceAlias, "0MATERIAL", "200", "KEY")

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

lResult = Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "Off")

lResult = Application.Run("SAPSetRefreshBehaviour", "On")

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 09, 2014 at 02:47 PM

    Hi Lachlan,

    on the first glance I can't see the Problem either...

    You could use this coding, to check the error message:

    Example (you can replace "DS_1"):

    Dim lResult As Variant
    Dim lError As Variant

    lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")
    lError = Application.Run("SAPGetProperty", "LastError")
    If lError(1) <> 0 Then
    'an error occured, display the code and text of it
    lMsg = Application.Run("SAPAddMessage", "Error: " & lError(1) & " " & lError(2), "ERROR")
    Else
    lResult = Application.Run("SAPExecuteCommand", "ShowPrompts", "DS_1")
    End If

    Just replace the Refresh by your SAPSetFilter.

    Btw.: You should include always the coding part to refresh your datasource, before you set any filter or variable!

    Best regards,

    Martin

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Martin,

      Thanks for your assistance.

      I ran your code, and the SAPSetFilter command actually worked. It was the same as my other code except I was the following line

      lResult = Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")

      When I remove this line from the code above the SAPSetFilter command works. My understanding of this line was that it just stopped the workbook from refreshing the filters hence allowing multiple filters to be changed before refreshing the data. It seems this is not the case.

      Any comments appreciated.

      Thanks

      Lachlan

  • Sep 09, 2014 at 03:33 AM

    Hi Lachlan Stormon,

    which version of Analysis Office you are using

    I tried with 1.4 SP7 & 1.4 SP8 works fine for me

    Dim lResult As Long

    lResult = Application.Run("SAPSetFilter", "DS_1", "0BC_VEND1__0BC_EVAL", "3", "KEY")
    MsgBox lResult

    Regards,

    Subhash


    Capture.PNG (2.5 kB)
    Add comment
    10|10000 characters needed characters exceeded