Can you use vba to automate essbase retrieval?

[smartbutdull]

I want to write some vba scripts to run before I come into the office to open and update some spreadsheets which pull data from Essbase.

Does anyone know if it's possible to connect to the essbase source using vba without popping up a log-in screen?

I've found the following code to connect and retrive, but I still need to manually punch in the password:

X = EssMenuVConnect()
X = EssMenuVRetrieve()

Any ideas?

Thanks,

Phil

 

[ETID]

If there is an essbase odbc driver...

Then setup a "dsn" for the connection (control panel>admin. tools>data sources(odbc)), the dsn properties should allow you to select an option to pass the login name and pword. to your db.

then in excel, use Data>Getexternal data> and select your dsn,...build your query from there.

 

[smartbutdull]

I've worked it out - if anyone else uses Hyperion Essbase and is interested the following code seems to do the trick:

Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long
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

Sub update()
'
' update Macro
'
'
Sheets("Essbase_Data&quot.Select
X = EssVConnect("Essbase_Data", "username", "password", "server", "application", "database&quot
X = EssMenuVRetrieve()
End Sub


Phil

(PS why is it that no matter how long I spend trying to work something out, I always find the answer just after I've posted the question on a forum?)

 

[SQLBI]

Phil,

I have a list of VBA declarations which I store in a module of any workbook i need to get data into, theres then no need to separately declare each function. If you'd like to post an email address i'll gladly send it to you.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[ryDen]

Hi,

Just like to check with you guys, what's the difference between EssMenuVRetrieve and EssVRetrieve? If I have a connection already, and I would like to retrieve data for only a certain part of a worksheet, which one should I use?

When doing manually, the user would select the range for which data is to be retrieved, and then click on retrieve from the Essbase menu. If I want to do this through vba, am I correct if I select the range, and then call the EssMenuVRetrieve function instead of the other one?

Thanks in advance.


Regards,
Russ.