Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to send to Essbase
Attempted to record a macro while sending data to Hyperion Essbase via the
Excel Add-In. However, the macro did not record my steps. Is there a way to accomplish what I am trying to do? Is there generic code to send directly to Essbase? What about something that will simply record the keystrokes to activiate the send how a regular end-user would? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to send to Essbase
Pablo
1)I'm assuming you've got the EssbaseDeclarations module (available from hyperion) in your VBA project 2)Listed below is everything I use to: -Test for an EB connection -Establish an EB connection -Lock/Send a range or worksheet to Essbase Hope you can adapt it to your needs. *************** BEGINNING OF CODE *************** 'Define global variables to be set in the course of processing Global prmUsername As String 'Stores the EB Username Global prmPassword As String 'Stores the EB password Global prmServer As String 'Stores the EB server to be accessed Global prmApplication As String 'Stores the EB application to be accessed Global prmDatabase As String 'Stores the EB database to be accessed Global blnKeepProcessing As Boolean 'General processing status flag Function Connect2EB(strShtName As String) As Boolean Dim lngConnReturn As Long Dim x As Variant Dim strMsgTxt As String Dim blnRetVal As Boolean 'set Essbase error messages to none EssVSetGlobalOption 5, 4 lngConnReturn = _ EssVConnect( _ sheetName=strShtName, _ username=prmUsername, _ password=prmPassword, _ server=prmServer, _ application=prmApplication, _ database=prmDatabase) If lngConnReturn 0 Then blnRetVal = False strMsgTxt = Essbase Login - Local Failure ElseIf lngConnReturn 0 Then blnRetVal = False strMsgTxt = Essbase Login - Server Failure Else blnRetVal = True strMsgTxt = Success End If Connect2EB = blnRetVal End Function Function EB_Connected() As Boolean Dim lngConnError As Long lngConnError = EssVGetHctxFromSheet(Null) 'check for Essbase connection If lngConnError 0 Then 'If 0 then there is an active connection to essbase EB_Connected = True Else EB_Connected = False End If End Function Function UploadEssbaseDataWithConn( _ strDBName As String, _ strAppName As String, _ strDestName As String, _ strDestType As String _ ) As Boolean Dim varLockResult As Variant Dim varSendResult As Variant Dim varOverallResult As Variant Dim strSheet As String Dim rngRef As Range Dim intLockFlag As Integer 'lockFlag action '1 Retrieves data and does not lock cells. '2 Locks the affected cells in the database and retrieves data. '3 Locks the affected cells in the database and does not retrieve data. Select Case strDestType Case Range On Error Resume Next 'The sheet containing the range is the range's parent strSheet = Range(strDestName).Parent.Name Set rngRef = Range(strDestName) Case Sheet On Error Resume Next strSheet = strDestName Set rngRef = Nothing End Select On Error GoTo 0 'Is there ANY connection to Essbase 'or are we connecting to a different app or db 'than the previous data pullloadcalc If EB_Connected = False _ Or _ prmDatabase strDBName _ Or _ prmApplication strAppName _ Then 'Set the global parameters to the NEW values prmDatabase = strDBName prmApplication = strAppName 'Establish a connection to Essbase If Connect2EB(strShtName=strSheet) = False Then 'Stop processing the current upload varOverallResult = True Else 'Upload data to Essbase Select Case strDestType Case Range intLockFlag = 3 '3=Lock impacted data WITHOUT retrieving varLockResult = EssVRetrieve( _ sheetName=strSheet, _ Range=rngRef, _ lockFlag=intLockFlag) If varLockResult = 0 Then 'Data lock engaged successfully, continue processing varSendResult = EssVSendData( _ sheetName=strSheet, _ Range=rngRef) If varSendResult = 0 Then 'Data loaded successfully. 'Time to unlock the data. varLockResult = EssVUnlock(sheetName=strSheet) If varLockResult 0 Then 'Could not release the db lock 'TO DO -Consider sending a comment somehow varOverallResult = False Else varOverallResult = True End If End If Else 'Data did not lock - DO NOT ATTEMPT TO SEND varOverallResult = False End If Case Sheet intLockFlag = 3 '3=Lock impacted data WITHOUT retrieving varLockResult = EssVRetrieve( _ sheetName=strSheet, _ Range=Null, _ lockFlag=intLockFlag) If varLockResult = 0 Then 'Data lock engaged successfully, 'so continue processing varSendResult = EssVSendData( _ sheetName=strSheet, _ Range=Null) If varSendResult = 0 Then 'Data loaded successfully. 'Time to unlock the data. varLockResult = EssVUnlock(sheetName=strSheet) If varLockResult 0 Then 'Could not release the db lock 'TO DO -Consider sending a comment somehow varOverallResult = False Else varOverallResult = True End If End If Else 'Data did not lock - DO NOT ATTEMPT TO SEND varOverallResult = False End If End Select blnKeepProcessing = varOverallResult End If End If On Error GoTo 0 If blnKeepProcessing = True Then UploadEssbaseDataWithConn = True Else UploadEssbaseDataWithConn = False End If If application.ScreenUpdating = False Then application.ScreenUpdating = True End If End Function ********** END OF CODE ********** Good luck. You can contact me if you need assistance. -- Regards, Ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to send to Essbase
Very nice code. I would love to get your e-mail address so we can swap some
code. Let me know if that would be of interest to you. "Ron Coderre" wrote: Pablo 1)I'm assuming you've got the EssbaseDeclarations module (available from hyperion) in your VBA project 2)Listed below is everything I use to: -Test for an EB connection -Establish an EB connection -Lock/Send a range or worksheet to Essbase Hope you can adapt it to your needs. *************** BEGINNING OF CODE *************** 'Define global variables to be set in the course of processing Global prmUsername As String 'Stores the EB Username Global prmPassword As String 'Stores the EB password Global prmServer As String 'Stores the EB server to be accessed Global prmApplication As String 'Stores the EB application to be accessed Global prmDatabase As String 'Stores the EB database to be accessed Global blnKeepProcessing As Boolean 'General processing status flag Function Connect2EB(strShtName As String) As Boolean Dim lngConnReturn As Long Dim x As Variant Dim strMsgTxt As String Dim blnRetVal As Boolean 'set Essbase error messages to none EssVSetGlobalOption 5, 4 lngConnReturn = _ EssVConnect( _ sheetName=strShtName, _ username=prmUsername, _ password=prmPassword, _ server=prmServer, _ application=prmApplication, _ database=prmDatabase) If lngConnReturn 0 Then blnRetVal = False strMsgTxt = Essbase Login - Local Failure ElseIf lngConnReturn 0 Then blnRetVal = False strMsgTxt = Essbase Login - Server Failure Else blnRetVal = True strMsgTxt = Success End If Connect2EB = blnRetVal End Function Function EB_Connected() As Boolean Dim lngConnError As Long lngConnError = EssVGetHctxFromSheet(Null) 'check for Essbase connection If lngConnError 0 Then 'If 0 then there is an active connection to essbase EB_Connected = True Else EB_Connected = False End If End Function Function UploadEssbaseDataWithConn( _ strDBName As String, _ strAppName As String, _ strDestName As String, _ strDestType As String _ ) As Boolean Dim varLockResult As Variant Dim varSendResult As Variant Dim varOverallResult As Variant Dim strSheet As String Dim rngRef As Range Dim intLockFlag As Integer 'lockFlag action '1 Retrieves data and does not lock cells. '2 Locks the affected cells in the database and retrieves data. '3 Locks the affected cells in the database and does not retrieve data. Select Case strDestType Case Range On Error Resume Next 'The sheet containing the range is the range's parent strSheet = Range(strDestName).Parent.Name Set rngRef = Range(strDestName) Case Sheet On Error Resume Next strSheet = strDestName Set rngRef = Nothing End Select On Error GoTo 0 'Is there ANY connection to Essbase 'or are we connecting to a different app or db 'than the previous data pullloadcalc If EB_Connected = False _ Or _ prmDatabase strDBName _ Or _ prmApplication strAppName _ Then 'Set the global parameters to the NEW values prmDatabase = strDBName prmApplication = strAppName 'Establish a connection to Essbase If Connect2EB(strShtName=strSheet) = False Then 'Stop processing the current upload varOverallResult = True Else 'Upload data to Essbase Select Case strDestType Case Range intLockFlag = 3 '3=Lock impacted data WITHOUT retrieving varLockResult = EssVRetrieve( _ sheetName=strSheet, _ Range=rngRef, _ lockFlag=intLockFlag) If varLockResult = 0 Then 'Data lock engaged successfully, continue processing varSendResult = EssVSendData( _ sheetName=strSheet, _ Range=rngRef) If varSendResult = 0 Then 'Data loaded successfully. 'Time to unlock the data. varLockResult = EssVUnlock(sheetName=strSheet) If varLockResult 0 Then 'Could not release the db lock 'TO DO -Consider sending a comment somehow varOverallResult = False Else varOverallResult = True End If End If Else 'Data did not lock - DO NOT ATTEMPT TO SEND varOverallResult = False End If Case Sheet intLockFlag = 3 '3=Lock impacted data WITHOUT retrieving varLockResult = EssVRetrieve( _ sheetName=strSheet, _ Range=Null, _ lockFlag=intLockFlag) If varLockResult = 0 Then 'Data lock engaged successfully, 'so continue processing varSendResult = EssVSendData( _ sheetName=strSheet, _ Range=Null) If varSendResult = 0 Then 'Data loaded successfully. 'Time to unlock the data. varLockResult = EssVUnlock(sheetName=strSheet) If varLockResult 0 Then 'Could not release the db lock 'TO DO -Consider sending a comment somehow varOverallResult = False Else varOverallResult = True End If End If Else 'Data did not lock - DO NOT ATTEMPT TO SEND varOverallResult = False End If End Select blnKeepProcessing = varOverallResult End If End If On Error GoTo 0 If blnKeepProcessing = True Then UploadEssbaseDataWithConn = True Else UploadEssbaseDataWithConn = False End If If application.ScreenUpdating = False Then application.ScreenUpdating = True End If End Function ********** END OF CODE ********** Good luck. You can contact me if you need assistance. -- Regards, Ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to send to Essbase
Happy to oblige.
My e-mail's in my profile...just click my name at the top of one of my postings -- Regards, Ron "Jim Thomlinson" wrote: Very nice code. I would love to get your e-mail address so we can swap some code. Let me know if that would be of interest to you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Add-In Essbase | Excel Discussion (Misc queries) | |||
ESSBASE | Setting up and Configuration of Excel | |||
essbase add in for xl | Excel Discussion (Misc queries) | |||
Essbase + Excel + VBA | Excel Programming | |||
Essbase VBA Macros - Help | Excel Programming |