Is there a Macro to update an Essbase Query in Multiple Tabs?

[Grepees]

Hello you good people out there,

I am working in a file that has 82 tabs. In each of these tabs is an Essbase query that needs to be updated to a range of cells that are consistent in all 82 tabs. It becomes very cumbersome to update these files as one has to go through each 82 tabs, highlight the essbase download section and retrieve Essbase updates. Sometimes some of these tabs are missed and then hell breaks loose.......Is there a macro that i can run to update all tabs at once. I already tried highlighting all tabs to update in one tab with the hope that all the highlighted tabs will also update but it didn't work. Any help out there would be most appreciated. Thank you very much.

 

[dk]

Hi

It's been quite a few years since I've used Essbase but I know that it's possible to automate it with VBA. A quick Google search revealed this document: http://download.oracle.com/docs/cd/E17236_01/epm.1112/esb_ss_user.pdf

The EssVCalculate function looks like it might do what you need. I cannot test this but see how this works for you:

Declare Function EssVCalculate Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal calcScript As Variant, ByVal synchronous As Variant) As Long

Sub RunCalculate()
    Dim sht As Worksheet
    Dim RetVal As Variant
    Dim sStatusMsg As String


    For Each sht In ActiveWorkbook.Worksheets

        sht.Activate

        RetVal = EssVCalculate(Empty, "Default", False)

        If RetVal <> 0 Then
            sStatusMsg = sStatusMsg & "Sheet: " & sht.Name & " failed to calculate." & vbCr
        End If


    Next sht

    If Len(sStatusMsg) > 0 Then
        MsgBox "One or more sheets failed to calculate:" & vbCr & sStatusMsg, vbExclamation, "Essbase Error"
    Else
        MsgBox "All sheets calculated successfully.", vbInformation, "Success"
    End If



End Sub

HTH
DK

 

[Grepees]

Thank you very much for your replay DK. The Macro did not work. It came back and listed almost all the Tabs saying that Essbase could not update the tabs. Is there anything i should do besides just copying the VBA and trying to run the macro? Thanks

 

[dk]

Hmm, well it was a long shot. There is some information here that might be useful: http://forums.oracle.com/forums/thread.jspa?threadID=2176698&tstart=1

Which version are you using? Does "Smartview" mean anything to you?

HTH
DK