Question on using SAPBEXsetFilterValue in Excel for BI 7.0 (not 3.5)

[Former Member]

Dear Forum - members,

I am trying to find a solution on how to automate Excel BI Queries with VBA.

The task is to

1. set the filter values for the query

2. Use the refresh function of the predefined Query and

3. save the workbook.

My changing filtervalue would be 0comp_code, which will be set by a vba and also determines the filename.

Searching on this topic for quite a while by now, I found quite many answers and examples for BI 3.5 but none for BI 7.0 we currently use.

The XLA File shipped with our installation is BexAnalyzer.xla Support Package 5 Revision 1083, not sapbex.xla and it seems they differ.

The function I am using is SAPBEXsetFilterValue which then fails with error 1004 (Intersect property cannot be assigned (German message: Die Intersect Eigenschaft des Application Objektes kann nicht zugeordnet werden). A second call (with or without modification) fails at the subroutine WriteToTrace(iText As String).

I am calling the function via:

If Run("BexAnalyzer.xla!SAPBEXsetFilterValue", "0COMP_CODE", "", Sheets("Tabelle1").Range("I20:I20")) = 0 Then
Else
MsgBox "SetfilterValue Error"
End If

Cell I20 contains the value SOSS - which is the company code.

Maybe I am just misunderstanding on how to call the function? Can someone provide me with a small VBA example on setting the filter variables and executing / refreshing the query ?

Any hint is thankfully appreciated

Andreas Wildner

[Former Member]

Hi Andreas,

I am having this problem as well. I created an OSS message on March 20th and since it still has not been resolved I am assuming it is not an easy fix. I have yet to get an answer as to whether this function is still supported although I have asked it several times.

I have seen other postings on SDN with this problem but have yet to see a solution.

SAP did provide some code that seems to work in simple cases but we have yet to get it working with a hierarchy filter. I have included it here as perhaps it will work in your situation.

Sub <sub_name>

Dim dp As Object

Set dp = BEx.DataProviders.Item("DATAPROVIDER_1")

Dim dimension As Object

Set dimension = dp.Request.Dimensions.Item("0COMP_CODE")

Dim filters As Object

Set filters = dimension.filters

If Not BEx.ServerStateExists Then

MsgBox ' Please refresh before using this Makro'

Exit Sub

End If

Call filters.SetFilterValue("0002") 'this sets the filter value

Call BEx.FrontendUpdate 'this retrieves the current stat Call BEx.Render 'this renders the data

End Sub

Please reply if and when you resolve this problem.

Regards,

Kelly