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