Macro with Essbase Add-in
in [Programming]
|
Prev: Setting OnAction Property Fails
Next: bond convexity
From: Anolan on 12 Dec 2005 09:53 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
From: "Bernie Deitrick" <deitbe @ consumer dot on 12 Dec 2005 11:43 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 "Anolan" <Anolan(a)discussions.microsoft.com> wrote in message news:1AAC2025-DB63-4D1D-877D-80C1751FA3A2(a)microsoft.com... > 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
From: King on 12 Dec 2005 11:45 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.
From: Jim Thomlinson on 12 Dec 2005 11:56 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 -- HTH... Jim Thomlinson "Anolan" wrote: > 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
From: Anolan on 13 Dec 2005 09:51
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" wrote: > 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. > > |