cancel
Showing results for 
Search instead for 
Did you mean: 

SAPSetFilter

Former Member
0 Kudos


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")

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Answers (1)

Answers (1)

former_member110741
Product and Topic Expert
Product and Topic Expert

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

Former Member
0 Kudos

Hi Subhash,

Thanks for the reply.

I am using version BO 4.0 SP7

I thought it might have something to do with a setting. I am quite new to this.

Not sure if there are any known issues of this type with SP7

Regards
Lachlan

former_member110741
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi ,

I don't think there are any settings are required for VBA

lets see if any other is having any other ideas to check

meanwhile you could probably check with AO 1.4 SP8

Regards,
Subhash