Recording Essbase Macros

Howdy, you'll want to look into Hyperion's devloped API's.

Here's a solid coder, Ivan F implimenting a few api (see the delcare statements) on something non-related:

http://www.mrexcel.com/board/viewtopic.php?topic=9295&forum=2

Just for example.

Excel won't capture the necessary api commands when you're recording (e.g., retrieve). They should be listed in the Essbase help section. I believe there are examples to help you. Don't recommend the logon one (although you can automate password entry), a retrieve will password prompt.

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-06-25 07:23

Ok a stupid question. Is there online help available? I don't see a seperate help screen within Excel, or under the essbase menu. It could be our IS group didn't load the help files for the user (most likely).

So, if you click help, you don't see a menu that says Essbase Help?

If so, click on macros for a quick list, they define paramaters but not the required api references.

If you search the index for the appropriate routine, you can find the required info. E.g, retrieve:

<pre>
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</pre>

Place the declaration at the top of a module and run the RetData() routine. Works better if you set you members up in advance. :wink:

Hope this helps.

Duhhh, Thanks Nate. I found it. It was hidden as "Essbase Help", right where you told me to look.

Hyperion Essbase Spreadsheet Toolkit???

I've had some experience putting together basic connection and retrieval macros but still have nagging questions regarding the "Hyperion Essbase Spreadsheet Toolkit" additional license that the online help is apparently trying to push. Here's what I do know. First, I know for a fact that I don't have it on my machine and no such thing is present on the server side. Second, it hasn't kept me from achieving the basic functionality I've required so far.

So, my questions are: 1) What am I missing by not having this Spreadsheet Toolkit available to me? As far as I can tell all of the Essbase specific functions listed in online help (e.g. "EssVRetrieve") will work when woven into a VBA macro as long as you declare them at the top of the module by pasting in the contents of ESSXLVBA.TXT. 2) Is this upgrade really just a slick GUI front-end for all of the stuff that can otherwise be accomplished in VBA by declaring and calling the Essbase specific functions listed in Help? 3) Does it install API's or something that would help centralize and distribute automated functionality? 4) Why would I buy the Hyperion Essbase Spreadsheet Toolkit if I'm comfortable declaring and calling Essbase functions in the VBA module and distributing this to users within Excel templates?

I'm vexed. The Help makes early and prominent mention of the Toolkit but really doesn't make distinctions as to what functionality is exclusive to this additional product license. Is it just Hyperion trying to separate me from more budget money?

Please advise.

Right, those functions are API calls which are registered with Windows when you install Essbase, I think (they're actuall in essexcln.xll in the ...Essbase\Bin directory (local)).

I have yet to muck with the toolkit or other support programs, Essbase for dummies.

I have used and distributed spreadsheets with the appropriate API declarations and if Essbase is properly installed on the client, all is well. Actually better then well, Excel and Essbase is a slick OLAP DB/Excel interface in my opinion.

Edit: Typo

Hi NateO,

Trying to run a macro essbase retrieve...

I tried following this code:
X = EssRetrieve("'Source Data'!", "'Source Data'!A1:G38", 1)
If X = 0 Then
MsgBox ("Retrieve successful.")
Else
MsgBox ("Retrieve failed.")
End If

And the error I am receiving is : Compile error: Sub or Function not defined.

Where am I going wrong?

**Note** I used EssVRetrieve and got the same error... i dropped the 'V' since the essbase help macro list didnt display a function with the "V" present.