Hyperion Essbase QAT Menu with Excel 2007

R

Risk

Board Regular
Joined
Jul 27, 2006
Messages
71
Hi,

I just upgraded to Hyperion Essbase 11 and I recently upgraded to Excel 2007. Moving to XL2007 made the Essbase add-in two levels deep in the ribbon so I wanted to move this to the Quick Access Toolbar (QAT). I found a great tool to develop QAT menus authored by Ron Debruin

I took his code and customized it to work with the Hyperion commands.
X7zFS.jpg

Everything worked great for a few month for both myself and about 75 coworkers but once we upgraded to Essbase 11 from 9, the menu macros stopped working for almost everyone except for myself and a few others.

I set this macro in the xlstart folders so that it opens with excel. The QAT menu still appears for everyone, but when selecting any of the menu items to run, nothing runs.

Example: When running
Code: 
Sub MyMacro21()
    Call EssMenuVConnect
End Sub
from the custom QAT menu, nothing happens. What should occur is a popup login for Hyperion. I've even tried to run the code outside of the QAT menu and it does not work so this leads me to believe something is wrong with my Hyperion code. Now mind you, this work fine on my machine.

I'm unable to determine the cause for the macro to not work. Any help or troubleshooting pointers would be appreciated.

Here is the code that I'm using.

Thanks in advance for any help.
-Risk

First Module (Hyperion Code and subs for the QAT menu)

Code: 
Option Explicit
Option Private Module


Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVKeepOnly Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVZoomIn Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVZoomOut Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVPivot Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVWizard Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVQueryDesigner Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVFlashBack Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVOptions Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVMemberSelection Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVCurrencyReport Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVCascade Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVRetrieveLock Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVLock Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVUnlock Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVSend Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVCalculation Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVConnect Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVDisconnect Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVDatalessNav Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVLinkedObjects Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVRemoveOnly Lib "ESSEXCLN.XLL" () As Long
Declare Function EssMenuVLaunchTableau Lib "ESSEXCLN.XLL" () As Long

' The following prototype declares the VBA-specific functions

Declare Function EssVGetHctxFromSheet Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVGetMemberInfo Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal mbrName As Variant, ByVal action As Variant, ByVal aliases As Variant) As Variant
Declare Function EssVFreeMemberInfo Lib "ESSEXCLN.XLL" (ByRef memInfo As Variant) As Long
Declare Function EssVFreeDataPoint Lib "ESSEXCLN.XLL" (ByRef Info As Variant) As Long

' The following constants are suitable as actions
' for the EssVGetMemberInfo function

Const EssChildLevel = 1
Const EssDescendentLevel = 2
Const EssBottomLevel = 3
Const EssSiblingLevel = 4
Const EssSameLevel = 5
Const EssSameGenerationLevel = 6
Const EssCalculationLevel = 7
Const EssParentLevel = 8
Const EssDimensionLevel = 9

' The following prototypes declare the Visual Basic
' Extended Spreadsheet macro-equivalent functions

Declare Function EssVCalculate Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal calcScript As Variant, ByVal synchronous As Variant) As Long
Declare Function EssVCancelCalc Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVCascade Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant, ByVal path As Variant, ByVal prefix As Variant, ByVal suffix As Variant, ByVal level As Variant, ByVal openFile As Variant, ByVal copyFormats As Variant, ByVal overwrite As Variant, ByVal listFile As Variant) As Long
Declare Function EssVCell Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ParamArray memberList() As Variant) As Variant
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 EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVFlashBack Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVGetCurrency Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Variant
Declare Function EssVGetDataPoint Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal cell As Variant, ByVal range As Variant, ByVal aliases As Variant) As Variant
Declare Function EssVGetGlobalOption Lib "ESSEXCLN.XLL" (ByVal item As Long) As Variant
Declare Function EssVGetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal item As Variant) As Variant
Declare Function EssVKeepOnly Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant) As Long
Declare Function EssVRemoveOnly Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant) As Long
Declare Function EssVPivot Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal startPoint As Variant, ByVal endPoint 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 EssVSendData Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant) As Long
Declare Function EssVSetCurrency Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal currencyIdentifier As Variant) As Long
Declare Function EssVSetGlobalOption Lib "ESSEXCLN.XLL" (ByVal item As Long, ByVal globalOption 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 EssVUnlock Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long
Declare Function EssVZoomIn Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant, ByVal level As Variant, ByVal across As Variant) As Long
Declare Function EssVZoomOut Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal selection As Variant) As Long
Declare Function EssVSetMenu Lib "ESSEXCLN.XLL" (ByVal setMenu As Boolean) As Long
Declare Function EssVGetStyle Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal styleType As Variant, ByVal dimName As Variant, ByVal item As Long) As Variant
Declare Function EssVSetStyle Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal styleType As Variant, ByVal dimName As Variant, ByVal item As Long, ByVal newValue As Variant) As Long
Declare Function EssVLoginSetPassword Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal newPassword As Variant, ByVal oldPassword As Variant, ByVal server As Variant, ByVal userName As Variant) As Long
Declare Function EssVSetSKUMouse Lib "ESSEXCLN.XLL" (ByVal fSKUWithMouse As Variant) As Long


Sub MyMacro1()
    Call EssMenuVRetrieve
End Sub

Sub MyMacro2()
    Call EssMenuVKeepOnly
End Sub

Sub MyMacro3()
    Call EssMenuVRemoveOnly
End Sub

Sub MyMacro4()
    Call EssMenuVZoomIn
End Sub

Sub MyMacro5()
    Call EssMenuVZoomOut
End Sub

Sub MyMacro7()
    Call EssMenuVDatalessNav
End Sub


Sub MyMacro9()
    Call EssMenuVLinkedObjects
End Sub

Sub MyMacro12()
    Call EssMenuVFlashBack
End Sub

Sub MyMacro13()
    Call EssMenuVOptions
End Sub

Sub MyMacro14()
    Call EssMenuVMemberSelection
End Sub

Sub MyMacro15()
    Call EssMenuVCurrencyReport
End Sub


Sub MyMacro17()
    Call EssMenuVLock
End Sub

Sub MyMacro18()
    Call EssMenuVUnlock
End Sub

Sub MyMacro19()
    Call EssMenuVSend
End Sub

Sub MyMacro20()
    Call EssMenuVCalculation
End Sub

Sub MyMacro21()
    Call EssMenuVConnect
End Sub

Sub MyMacro22()
    Call EssMenuVDisconnect
End Sub

Sub MyMacro23()
    Call SHRetrieveRetain
End Sub

Sub MyMacro24()
    Call WBRetrieveRetain
End Sub

Sub MyMacro25()
    Call SHRetrieveSuppress
End Sub

Sub MyMacro26()
    Call WBRetrieveSuppress
End Sub


Second Module (this is Ron Debruin's QAT code)

Code: 
Option Explicit
Option Private Module


'TIP:

'After you click on "Edit Menu" in the menu you can change the button image.
'Right click on the QAT and choose Customize Quick Access Toolbar.
'In the “Choose commands from” dropdown choose Macros and in the
'Customize Quick Access Toolbar dropdown choose "For My Add-in.xlam".
'Select the RDBDisplayPopUp macro and press the Modify button to change the icon.


Sub WBCreatePopUp()
    Dim MenuSheet As Worksheet
    Dim MenuItem As Object
    Dim SubMenuItem As CommandBarButton
    Dim Row As Integer
    Dim MenuLevel, NextLevel, MacroName, Caption, Divider, FaceId

    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    '   Location for menu data
    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
    ''''''''''''''''''''''''''''''''''''''''''''''''''''

    '   Make sure the menus aren't duplicated
    Call WBRemovePopUp

    '   Initialize the row counter
    Row = 5

    '   Add the menu, menu items and submenu items using
    '   data stored on MenuSheet

    ' First we create a PopUp menu with the name of the value in B2
    With application.CommandBars.Add(ThisWorkbook.Sheets("MenuSheet"). _
                                     range("B2").Value, msoBarPopup, False, True)

        Do Until IsEmpty(MenuSheet.Cells(Row, 1))
            With MenuSheet
                MenuLevel = .Cells(Row, 1)
                Caption = .Cells(Row, 2)
                MacroName = .Cells(Row, 3)
                Divider = .Cells(Row, 4)
                FaceId = .Cells(Row, 5)
                NextLevel = .Cells(Row + 1, 1)
            End With

            Select Case MenuLevel
            Case 2    ' A Menu Item
                If NextLevel = 3 Then
                    Set MenuItem = .Controls.Add(Type:=msoControlPopup)
                Else
                    Set MenuItem = .Controls.Add(Type:=msoControlButton)
                    MenuItem.OnAction = ThisWorkbook.Name & "!" & MacroName
                End If
                MenuItem.Caption = Caption
                If FaceId <> "" Then MenuItem.FaceId = FaceId
                If Divider Then MenuItem.BeginGroup = True

            Case 3    ' A SubMenu Item
                Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = Caption
                SubMenuItem.OnAction = ThisWorkbook.Name & "!" & MacroName
                If FaceId <> "" Then SubMenuItem.FaceId = FaceId
                If Divider Then SubMenuItem.BeginGroup = True
            End Select
            Row = Row + 1
        Loop
    End With
End Sub

Sub RDBDisplayPopUp()
    On Error Resume Next
    application.CommandBars(ThisWorkbook.Sheets("MenuSheet").range("B2").Value).ShowPopup
    On Error GoTo 0
End Sub

Sub EditMenu()
    ThisWorkbook.IsAddin = False
End Sub

Sub WBRemovePopUp()
    On Error Resume Next
    application.CommandBars(ThisWorkbook.Sheets("MenuSheet").range("B2").Value).Delete
    On Error GoTo 0
End Sub

Third Module (some custom Essbase comands to toggle between "retain formula options" and "suppress zero options")

Code: 
Option Explicit

Sub WBRetrieveRetain()

Dim sh As Worksheet, HidShts As New Collection 'to handle hidden sheets
    For Each sh In ActiveWorkbook.Worksheets
        If Not sh.Visible Then
            HidShts.Add sh
            sh.Visible = xlSheetVisible
        End If
    Next sh

For Each sh In Worksheets
    Sheets(sh.Name).Activate

  
   'Turn On Retain and Turns off Suppress and double clicks
    If EssVGetSheetOption(Empty, 6) = True Or _
       EssVGetSheetOption(Empty, 7) = True Then
    
       Call EssVSetSheetOption(Empty, 6, False)
       Call EssVSetSheetOption(Empty, 7, False)
    End If

    If EssVGetGlobalOption(1) = True Or _
       EssVGetGlobalOption(2) = True Then

       Call EssVSetGlobalOption(1, False)
       Call EssVSetGlobalOption(2, False)
    End If

    Call EssVSetSheetOption(Empty, 11, True)
    Call EssVSetSheetOption(Empty, 21, True)
    Call EssVSetSheetOption(Empty, 22, True)
       

  Next


    For Each sh In HidShts
        sh.Visible = xlSheetHidden
    Next sh

       

End Sub

Sub SHRetrieveRetain()
 
   'Turn On Retain and Turns off Suppress and double clicks
    If EssVGetSheetOption(Empty, 6) = True Or _
       EssVGetSheetOption(Empty, 7) = True Then
           
       Call EssVSetSheetOption(Empty, 6, False)
       Call EssVSetSheetOption(Empty, 7, False)
    End If

    If EssVGetGlobalOption(1) = True Or _
       EssVGetGlobalOption(2) = True Then

       Call EssVSetGlobalOption(1, False)
       Call EssVSetGlobalOption(2, False)
    End If

    Call EssVSetSheetOption(Empty, 11, True)
    Call EssVSetSheetOption(Empty, 21, True)
    Call EssVSetSheetOption(Empty, 22, True)

End Sub

Sub SHRetrieveSuppress()
 
   'Turn Off Retain and Turns on Suppress
    If EssVGetSheetOption(Empty, 11) = True Or _
       EssVGetSheetOption(Empty, 21) = True Or _
       EssVGetSheetOption(Empty, 22) = True Then
    
       Call EssVSetSheetOption(Empty, 11, False)
       Call EssVSetSheetOption(Empty, 21, False)
       Call EssVSetSheetOption(Empty, 22, False)
    End If


    Call EssVSetSheetOption(Empty, 6, True)
    Call EssVSetSheetOption(Empty, 7, True)
    
End Sub

Sub WBRetrieveSuppress()

Dim sh As Worksheet, HidShts As New Collection 'to handle hidden sheets
    For Each sh In ActiveWorkbook.Worksheets
        If Not sh.Visible Then
            HidShts.Add sh
            sh.Visible = xlSheetVisible
        End If
    Next sh

For Each sh In Worksheets
    Sheets(sh.Name).Activate

 
   'Turn Off Retain and Turns on Suppress
    If EssVGetSheetOption(Empty, 11) = True Or _
       EssVGetSheetOption(Empty, 21) = True Or _
       EssVGetSheetOption(Empty, 22) = True Then
    
       Call EssVSetSheetOption(Empty, 11, False)
       Call EssVSetSheetOption(Empty, 21, False)
       Call EssVSetSheetOption(Empty, 22, False)
    End If


    Call EssVSetSheetOption(Empty, 6, True)
    Call EssVSetSheetOption(Empty, 7, True)
          
  Next

    For Each sh In HidShts
        sh.Visible = xlSheetHidden
    Next sh
       

End Sub


Excel Object Code
Code: 
Option Explicit

Private Sub CommandButton1_Click()
    Call WBCreatePopUp
    MsgBox "Click on the button in the QAT to see if your menu is correct.", vbOKOnly, "Favorite Macro Menu"
End Sub

Private Sub CommandButton2_Click()
    Call WBCreatePopUp
    range("A1").Select
    ThisWorkbook.IsAddin = True
    ThisWorkbook.Save
End Sub

Private Sub CommandButton3_Click()
    ThisWorkbook.IsAddin = True
    ThisWorkbook.Saved = True
End Sub

Code: 
Option Explicit

Private Sub Workbook_Open()
    Call WBCreatePopUp
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call WBRemovePopUp
End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
You must log in or register to reply here.

Similar threads

Jaafar Tribak
  • Question Question
KeyPress Event for Worksheet Cells (Subclassing Without Crashing)
Replies
0
Views
337
Jaafar Tribak
Jaafar Tribak
Jaafar Tribak
  • Question Question
Capturing the Paste Event (API Hooking - Redirection)
Replies
4
Views
850
Jaafar Tribak
Jaafar Tribak
M
  • Question Question
Please help me to correct Excel VBA code!
Replies
1
Views
346
Minh Tuan VN
M
Jaafar Tribak
  • Question Question
Blurring images using Direct2D & Windows Imaging Component
Replies
3
Views
820
Dan_W
D
A
  • Question Question
What is wrong with this VBA code: userForm get hand ico for pointer on hover
2
Replies
15
Views
1K
RoryA
RoryA

Forum statistics

Threads
1,224,585
Messages
6,179,704
Members
452,938
Latest member
babeneker

Share this page

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