There is a whole bunch of Analysis Office APIs returning a table / list of data. These are e.g. all functions starting with SAPListOf*, SAPGetDisplayedMeasures or SAPGetCellInfo.
The example provided uses the SAPListOfEffectiveFilters function to read all effective filters from data source DS_1 and show them line by line in Message Boxes.
Sub sample()
Dim lResult
lResult = Application.Run("SAPListOfEffectiveFilters", "DS_1", "TEXT")
If Not IsError(lResult) Then
Dim i As Integer
Dim lLine
On Error Resume Next
i = 0
Do
i = i + 1
lLine = ""
lLine = WorksheetFunction.Index(lResult, i, 0)
If IsArray(lLine) = False Then Exit Do
' add your coding here to do something with the line
MsgBox (Join(lLine, " - "))
Loop
On Error GoTo 0
End If
End Sub
|
Testing the Sample Code:
If you like to test the code snipped, insert a query to your Analysis Office workbook and restrict some dimensions. Executing the code snipped should show the restrictions in a series of prompts.
Some comments to the implementation:
If the API call returns just one line it comes as a one-dimension array (like dim a(3)), if several lines are returned it comes as a two-dimensional array (like dim a(2,3)). Using the Index function to cut the n-th row out of the result set hides the different cardinality and return a one-dimensional array only, reflecting the content of a row.
The VBA language doesn't offer a function or property to ask for the number of rows respective the cardinality. Hence the sample increases the row number until the index function fails. In this case the IsArray() function returns a false - triggering an Exit Do.
