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