VBA for Essbase Hyperion

[aja4481]

Hi all. I'm now using Hyperion Essbase within Excel and I was wondering if anyone out there had any coding that could be helpful for pulling data. So I have the below code to refresh a data page but I'm still trying to find helpful tools to automate data pulling.

Any help would be appreciated!

Sub Hyperion_Refresh()
Sheets("Sheet1").Select
Range("A1:I9").Select
Application.Run macro:="EssMenuRetrieve"

 

[Oaktree]

I'd usually use something like this:

Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal userName As Variant, ByVal password As Variant, _
	ByVal Server As Variant, ByVal Application As Variant, ByVal Database As Variant) As Long
Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal lockFlag As Variant) As Long
Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal item As Long, ByVal globalOption As Variant) As Long

Sub GetUpdatedData()

Call EssVSetGlobalOption(6, False) 

Call EssVConnect("Sheet_Name_Goes_Here", "User_Name_Goes_Here", "Password_Goes_Here", _
	"Server_goes_here", "Application_Listed_on_the_Left_goes_here", _
	"Database_Listed_on_the_Right_goes_here")
Call EssVRetrieve("Sheet_Name_Goes_Here", range("Range_of_Essbase_Query_goes_here_e.g.A4:O9"), Null)
Call EssVDisconnect("Sheet_Name_Goes_Here")

‘Repeat (adjusting as necessary) the three Call statements above as many times as needed for your workbook

Call EssVSetGlobalOption(1, False) 
Call EssVSetGlobalOption(2, False) 
Call EssVSetGlobalOption(6, True)
End Sub

 

[aja4481]

Thank you. But without sounding naive, what function will that perform?

 

[Oaktree]

It will connect to the database, retrieve the data, and disconnect from the database.

 

[aja4481]

just curious, why is so much code needed to log in? the initial code I posted logs you in and then you can use the below code to disconnect

Sub Disconnect_US()
Sheets("Data Sheet").Select
Range("A1:X241").Select
Application.Run macro:="EssMenuDisconnect"

 

[aja4481]

also, when i tried to copy and paste the code i was getting error messages in the de-bugger