Hyperion Essbase Knowledgebase

Essbase Retrieve all Sheets Example

User Rating:  / 1

Lets see an example of how Essbase and VBA can make your life easier.

Let's say you have made an Excel workbook that has 10 sheets in it. Each month you would like to update the entire workbook. So the manual steps would be to open the workbook, then perform an Essbase Retrieve on each sheet. This would be a little tedious on a monthly or even weekly basis.

Lets look at the code to automate this. (full code listing is at the bottom of this article)

We start out with the command that performs the Essbase Retrieve operation.

x = EssVMenuRetrieve

When you run this code, it is exactly the same as selecting Essbase Retrieve with the mouse.

So, how are we going to loop through all the sheets?

Num_Sheets = application.Sheets.Count
For y = 1 To Num_Sheets
Next x

application.sheets.count will return the number of sheets in the current workbook. This value is stored in a variable called Num_Sheets. Now lets say there are 100 sheets in the workbook. This code would not need to change at all.

Each sheet in Excel has a sheet number attached to it. The numbers start with 1 and increase by 1.

Sheets(y).select will make the sheet with that number active (it will actually select the sheet and make it visible).

So, the command sheets(2).select will select the sheet with the index number 2.

for y = 1 to num_sheets

next y

This is a for next loop that will execute the commands within the specified number of times. Since num_sheets = 10, this loop will execute 10 times.

So we want to:

  1. get the count of number of sheets in the workbook,
  2. select each sheet
  3. perform a retrieve operation
  4. Redo these steps as many times as there are sheets in the workbook.

You can just copy the following code into a module in Excel.

Lets test this out. First open a new workbook in Excel.

Make sure you can connect to your Essbase server and perform a Retrieve against your database.

Now select Alt-F11, or Tools Macro, Visual Basic Editor.

Its a good idea to half minimize the VBA window so you can see Excel in the background. You can step through your code and watch the effects in Excel at the same time.

From the menu, select Insert Module

Then copy and paste the required code into the window.

To run your code, select from the menu, Debug, Step Into (f8). Each time you select this option your code will run one more line of code. The code to run will start with the procedure your cursor is currently residing in. If your cursor is not on or within a procedure, no code will run.

You need to hit f8 until the yellow highlighted text is gone. This indicated your program has completed. This little program will work for any number of sheets in a workbook. One requirement with this code is that every sheet in a workbook must be retrievable.

Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long,
Sub loop_all_sheets()
Num_Sheets = application.Sheets.Count
' The number of sheets in the workbook are stored in the Num_Sheets variable
For y = 1 To Num_Sheets
'This is a loop that will repeat Num_Sheets times. If Num_Sheets is 10 then the
'loop will execute 10 times.
x = EssMenuVRetrieve
Next x
End Sub

There is a screencam movie that walks through the creation of this example.  Take a look at Essbase Retrieve All Sheets VBA Example.

Additional information