VBA Code, Lock & Send Essbase Add-in

deanexcel

New Member
Joined
Jul 20, 2010
Messages
9
Hi Guys,

Ive been looking around the net for code to lock and send to the back end cubes of Excel.

I have sucessfully 'adjusted' a retrieval code where the user enters their username and password into a designated cell connects via a button and retrieves all data in the parameters selected. Ive given everything range names.

Below is the code I have to retrieve. I am looking for something similar to this to connect to Essbase and Lock & Send (I have seen a few examples including the example in the Hyperion folder on my HD, but I cannot seem to get these examples to fit with what I am doing.

I hope someone here could help once you see the coding below.


__________________________________________________________

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 EssVSetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal item As Variant, ByVal sheetoption 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 EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal item As Long, ByVal globalOption As Variant) As Long
Declare Function EssVSendData Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal Range As Variant) As Long

Dim ServerName, User, Pass, App, Db As Variant


' retrieve_all Macro
' Macro recorded 07/15/10 by DeanT
'
'

Sub Get_User_Info1()
ServerName = Range("Server1").Value
User = Range("Username1").Value
Pass = Range("Password1").Value
Db = Range("Database1").Value
App = Range("Application1").Value
End Sub
Sub Connect_to_CUBE()
Sheets("sheet1").Select
X = EssVConnect(Null, User, Pass, ServerName, App, Db)
If X <> 0 Then No_Connect_Alert = _
MsgBox("TEMPLATE WAS UNABLE TO CONNECT TO REGIONALCUBE." & Chr$(13) & "Please Verify Your User ID and Password.", 16, "UNABLE TO CONNECT TO SERVER"): _
Return_to_Intro: End
X = EssVSetSheetOption(Null, 9, "0") 'Set missing item to "0" (zero)
X = EssVSetSheetOption(Null, 11, True) 'Set Preserve Formulas ON
X = EssVSetSheetOption(Null, 12, False) 'Set Adjust Columns settings to "off"
X = EssVSetGlobalOption(5, 3) 'Set Message level to "Error Messages Only"
X = EssVSetGlobalOption(6, False) 'Set Display-unknown settings to "off"
Sheets("sheet1").Select
X = EssVConnect(Null, User, Pass, ServerName, App, Db)
If X <> 0 Then No_Connect_Alert = _
MsgBox("TEMPLATE WAS UNABLE TO CONNECT TO REGIONALCUBE." & Chr$(13) & "Please Verify Your User ID and Password.", 16, "UNABLE TO CONNECT TO SERVER"): _
Return_to_Intro: End
X = EssVSetSheetOption(Null, 9, "0") 'Set missing item to "0" (zero)
X = EssVSetSheetOption(Null, 11, True) 'Set Preserve Formulas ON
X = EssVSetSheetOption(Null, 12, False) 'Set Adjust Columns settings to "off"
X = EssVSetGlobalOption(5, 3) 'Set Message level to "Error Messages Only"
X = EssVSetGlobalOption(6, False) 'Set Display-unknown settings to "off"
End Sub
Sub Check_for_Password()




If Range("Password1").Value = "*****" Then _
No_Password = MsgBox("You Need to Enter Your Password to Continue. Please Try Again.", 16, "PASSWORD REMINDER"): _
Return_to_Intro: End
End Sub
Sub Return_to_Intro()
Sheets("Macros").Select
Range("Password1").Select
Range("Password1").Value = "*****"
End Sub
Sub DataPull_Current()
Get_User_Info1
Connect_to_CUBE
application.Goto Reference:="sheet1_data"
Range("sheet1_data").Select
Range("sheet1_data").ClearContents
application.Goto Reference:="sheet1_Ret"
X = EssVRetrieve(Null, Range("sheet1_Ret"), 1)
application.Goto Reference:="Seasonality_data"
Range("Seasonality_data").Select
Range("Seasonality_data").ClearContents
application.Goto Reference:="Seasonality_Ret"
X = EssVRetrieve(Null, Range("Seasonality_Ret"), 1)

Return_to_Intro
application.Goto Reference:="LastRetrieval"
Range("LastRetrieval").Select
Range("LastRetrieval").ClearContents
application.Goto Reference:="LastRetrieval"
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A5").Select
application.Goto Reference:="LastRetrieval"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub


If anyone can help thank you!!

Dean
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

mikeTRON

New Member
Joined
Sep 27, 2012
Messages
42
Throw back...

does anyone have an answer or help with a macro to Lock and SEND in Essbase?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,333
Messages
5,595,560
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top