Skip to Content
Author's profile photo Olaf Fischer
Olaf Fischer
June 30, 2011 2 minute read

Analysis Office 1.1 – Using the VBA APIs – Create in-Sheet Drop-Down populated with API results

It is quite common that the API of Analysis Office returns arrays containing the requested information.

Example: The workbook contains two Data Sources DS_1 and DS_2. The API call to read the list of Data Sources ( Application.Run(“SAPListOf”, “DATASOURCES”) ) will return the following information: 

DS_1 Description of DS_1
DS_2 Description of DS_2

 

A typical question that arises: Suppose I am only interessted in the first column ( in the example above: containing the data source alias) of the result set. How can I easily extract it? And how can I expose the content to the user?

Well lets answer it step by step by going through the sample coding:

After retreiving the list of DataSources from the API it checks for an empty result (IsArray()-function). Then it copies the first column (Index-function), transposes it into a row and converts it into a string containg the Data Source Alias, seperated by “,” (in our example, the result is “DS_1″,”DS_2”). Based on a named range as target, it creates the cell validation/drop down.

VBA – Source Code Sample

Option Explicit

Sub AO_UpdateDSList()

  ‘ get list of data sources
  Dim DataSources As Variant
  DataSources = Application.Run(“SapListOf”, “DATASOURCES”)
  
  ‘ extract data source alias (first column of result) and converts it to a string with comma seperated values
  Dim DDValues As Variant
  Dim DSAlias As Variant
  If IsArray(DataSources) Then
    DSAlias = Application.WorksheetFunction.Index(DataSources, 0, 1)
    DDValues = Join(Application.WorksheetFunction.Transpose(DSAlias), “,”)
  Else
    DDValues = DataSources
    If DDValues = “” Then DDValues = “No data sources found!”
  End If
  
  ‘ create/adjust the data validation for the named range “AOListOfDataSource”
  With Range(“AOListOfDataSource”).Cells.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=DDValues
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = “List of Data Source Alias”
        .ErrorTitle = “Invalid Data Source”
        .InputMessage = “Select a data source from the list of values”
        .ErrorMessage = “The entered data source doesn’t exist. Please enter a valid value”
        .ShowInput = True
        .ShowError = True
 End With
 
 ‘ clear current cell content
 Range(“AOListOfDataSource”).Value = “”
 
End Sub

 

Once you have defined the named range and created and executed the VBA subroutine successfully, the result should look similar to the screenshot (Hint: don’t forget to set the focus on the cell):

image

 

Outlook: Although the example is based on the list of data sources it can be used for other array based results as well, e.g. ListOfDimensions.

Assigned tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Olaf Fischer
      Olaf Fischer
      Blog Post Author
      I have been asked how this could be used for a list of members ("F4-Help"). Well it is pretty straight forward:

      1. Adjust the sample code by using the API function SAPListOfMembers("DS_1";"FILTER";"0PRODUCT")
      2. Adjust the .index statement to pick up the second column to get the member texts.

      That's it.

      Hint: if you need the member key - the API function SAPGetMember("DS_1";"0PRODUCT=LAPTOP";"KEY") converts the text to key.
      =SAPGetMember("DS_1";H16;"KEY")