Macro with Essbase Add-in

[Anolan]

Hello All,

I have a query setup in Excel that retrieves data from Hyperion using the
Essbase Add-In. Can anyone please show me how to write code that will
initiate the Retreive Data function automatically. I have a macro with input
boxes that will prompt the user to change the dimensions, but I do not know
how to automatically retreive the data via Essbase via a macro (without
manually selecting Essbase from the toolbar and clicking on Retrieve Data).

Thank you. Andy

 

[Bernie Deitrick]

Andy,

How it is done depends on how open the Essbase object is to VBA. Usually, set a reference to the
Add-In through Tools / References in the VBE, then declare the proper variables, and give it a go.
Essbase should have some examples on-line, or offer support, or have a help file, or a newsgroup,
or....

HTH,
Bernie
MS Excel MVP

 

[King]

Could you tell me what kind of query is that? Is it a Essbase
Query-Designer query? or Is it a worksheet with selected members
adjusted in rows and columns. if you could provide a sample example
then i can try to figure it out.

Regards.

 

[Jim Thomlinson]

Here is an excerpt from the Excel Addin Help. You should have this on your
system...

EssVRetrieve() retrieves data from the database and specifies locking
behavior.

Syntax

EssVRetrieve(sheetName, range, lockFlag)
ByVal sheetName As Variant
ByVal range As Variant
ByVal lockFlag As Variant

Parameters

sheetName
Text name of worksheet to operate on. sheetName is of the form
"[Book.xls]Sheet". If sheetName is Null or Empty, the active sheet is used.
range
Range object which refers to the data to be used as the source of the
retrieve. If range is Null or Empty, the whole sheet is used. Usually,
specifying Null is the best way to update the data in your sheet. However, if
you have a formatted sheet and you want to specify a range, then range needs
to be a combination of contiguous cells containing member names and data. Or
you can specify a blank range of cells for Hyperion Essbase to fill. The
range you specify should be big enough to display all the values returned.

lockFlag
Number indicating whether any blocks should be locked. The following table
indicates the valid values and their actions:

lockFlag Action
1 Retrieves data and does not lock cells.
2 Locks the affected cells in the database and retrieves data.
3 Locks the affected cells in the database and does not retrieve data.
If lockFlag is Null or Empty, 1 is used.

Return Value

Returns 0 if successful. A negative number indicates a local failure. A
return value greater than zero indicates a failure on the server.

Example

Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As
Variant, ByVal range As Variant, ByVal lockFlag As Variant) As Long

Sub RetData()
X=EssVRetrieve("[Book2.xls]Sheet1", RANGE("A1:F12"), 1)
If X = 0 Then
MsgBox("Retrieve successful.")
Else
MsgBox("Retrieve failed.")
End If
End Sub

 

[Anolan]

King,

Initially I created the query using the Essbase query designer. Afterwards,
I just change the member names and click Retrieve Data.

The member names run across the columns in Row 1. The entity name and
account run down the rows in column A.

I have Input boxes in my macro that once the user enters a value, it inputs
the value into the cell of the query spreadsheet.

But, now I want the macro to automatically "refresh" the data.

Thanks for you assistance...Andy

 

[King]

You can do one thing, created a module in your spreadsheet and write a
subroutine as follows,

-----------------------------------------------
Sub auto_open()
Call GetUpdatedData
End Sub

Sub GetUpdatedData()
Dim Server As String
Dim Application As String
Dim Database As String
Dim ID As String
Dim PW As String
Dim x As Long, y As Long

Server = your server string
Application = the name of the application you are connecting
Database = the name of the database under the application
ID = your user ID
PW = your Password

x = EssVConnect("[" & ThisWorkbook.Name & "]" & ActiveSheet.Name,
ID, PW, Server, Application, Database)
If x = 0 Then
y = EssVRetrieve(Null, Null, Null)
If y = 0 Then
MsgBox "Retrieve Successful"
Else
MsgBox "Retrieve Failed"
End Sub
End If
Else
MsgBox "Unable to Connect to the Server"
End If

End Sub
-----------------------------------------------------------

create another module and put all the essbase declarations in that
module ( for that you have to copy all containts of the file
"your_essbase_directory\bin\essxlvba.txt" in that module ). Set the
propers values for variables declared in 'GetUpdatedData' subroutine.
The macro "auto_open" will be called everythime you will open your
spreadsheet. so everytime when you open the spreadsheet, you will get
updated data. And put a line "Call GetUpdatedData" at the end of the
macro which you have created to update members. so data will be updated
again when you change your member name using the macro you have
created.

Hope this helps,

King