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.
Option Explicit Sub AO_UpdateDSList() ‘ 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.
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")