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
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
