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.
‘ get list of data sources
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):
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.