The SAMPVBA.XLS file (see Sample Code Listing) provides an example of how to use Essbase VBA functions with the Essbase VB API. This file is stored in /ESSBASE/CLIENT/SAMPLE when you install Spreadsheet Add-in. To use the code sample, you need Oracle's Hyperion® Essbase® Spreadsheet Toolkit and the Essbase API modules.
To successfully run a VB module that includes VB API function calls, you must include ESBAPIN.DLL in /ESSBASE/BIN. |
Remember, when using the Visual Basic API inside Excel, you must use a Dim statement for each argument listed in the declarations. Do not do this for the VBA functions.
Also, you must use ESB32.BAS as the guideline for structures and declarations in the Essbase Visual Basic API.
SAMPVBA.XLS is a spreadsheet with a worksheet for each of the months from January to August, plus a Main worksheet and a SampleVBA worksheet:
Each month worksheet is a grid of values with products on the left and states on the top.
On the Main worksheet are buttons that, when clicked, run a subroutine:
The SampleVBA worksheet is a module containing sample code.
If you use Excel to open SAMPVBA.XLS, you may encounter a dialog box warning you of opening macros (see Opening Spreadsheets Containing Macros in Excel). |
To use SAMPVBA.XLS in your environment, you must modify the sample code to reflect your user name and server name. In each of the subroutines, change the EssVConnect function located near the beginning of the subroutine to reflect your user name, password, and server name containing the Sample Basic application.
For example, if your user name is USER1 with password PASSWORD and the server name is LOCAL, change the EssVConnect statement as follows:
X=EssVConnect("[SAMPVBA.XLS]", "USER1", "PASSWORD", "LOCAL", "Sample", "Basic")