Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with Essbase Add-in
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with Essbase Add-in
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with Essbase Add-in
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with Essbase Add-in
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with Essbase Add-in
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with Essbase Add-in
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ESSBASE | Setting up and Configuration of Excel | |||
How do I program a macro to do an essbase retrieve? | Excel Programming | |||
Hyperion Essbase add-in macro | Excel Programming | |||
Macro for "Retrieve" in Essbase Menu | Excel Programming | |||
Macro to connect excel sheets automatically to Hyperion ESSBASE 6.5 | Excel Programming |