Skip to Content
Author's profile photo Former Member

Analysis Office 1.1 – Using the VBA APIs – Looping across an API result

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

Assigned tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.