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!

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

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

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.