Essbase Fails with Multiple Instances of Excel

It is well known that the Essbase client will not function properly if the user has opened multiple instances of Excel.  Sometimes this is particularly mysterious because there appears to be only one instance open…but task manager reveals that a second “phantom” instance is still loaded in memory.

Either way, the typical symptom is that an Essbase retrieve returns nothing…no data, no errors or even warnings. The retrieve may be in some sense “working” but it is working in an Excel instance other than the active, intended instance.

There is not much that you, the Essbase developer, can do about this if the user is running in adhoc mode. However, if you have developed a reporting application, you can call the Windows API to identify the problem.

I have written a VBA function which runs before attempting to produce reports requested by a user.  Simply, it checks for any Excel instances other than the current instance, and if found, notifies the user that reports cannot be produced until the problem is rectified.

I also open a simple step-by-step instructions document in notepad, explaining the process of going into task manager to find and kill any phantom instances of Excel. (Feel free to use the following code in any way you like. If this code, or this post, is helpful to you, I would appreciate a link back to this website. Thanks.)

Option Explicit

Declare Function GetCurrentProcessId Lib "kernel32" () _
    As Long

Declare Function GetDesktopWindow Lib "user32" () _
    As Long

Declare Function FindWindowEx Lib "user32" _
    Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, ByVal lpsz2 As String) _
    As Long

Declare Function GetWindowThreadProcessId Lib "user32" _
    (ByVal hwnd As Long, ByRef lpdwProcessId As Long) _
    As Long

Sub Test()

    If Not MultipleExcelInstances Then
        'run reports, etc.
    End If

End Sub

Function MultipleExcelInstances() As Boolean

    'Written by Fritz Barnes, Transcend Technologies, Inc.
    'http://www.transcend-tech.com

    Const PROMPT = "An instance of Excel, other than this one, was found." _
            & vbCrLf _
            & "Please close any other Excel instance, and retry." _
            & vbCrLf & vbCrLf _
            & "(Essbase will not function properly with multiple instances" _
            & vbCrLf _
            & "of Excel open.)"

    Dim hWndDesktop As Long
    Dim hwnd As Long
    Dim hMyProc As Long
    Dim hProcFound As Long
    Dim iResp As Integer
    Dim lReturnVal As Long
    Dim lExitCode As Long
    Dim lPid As Long
    Dim lProcess As Long

    Dim sClass As String
    Dim sCaption As String

    Const PROC_NAME = "MultipleExcelInstances"

    On Error GoTo MultipleExcelInstances_EH

    MultipleExcelInstances = False

    sClass = "XLMain"
    sCaption = vbNullString

    'get the instance this code is running in...want to know if there are others
    hMyProc = GetCurrentProcessId

    'parent of all top level windows is the desktop
    hWndDesktop = GetDesktopWindow

    Do
        'Loop thru children of the desktop
        hwnd = FindWindowEx(hWndDesktop, hwnd, sClass, sCaption)

        'get out if no more matching windows are found
        If hwnd = 0 Then
            Exit Do
        End If

        'get ProcessId for the matching window we just found
        GetWindowThreadProcessId hwnd, hProcFound

        If hProcFound <> hMyProc Then

            MultipleExcelInstances = True

            MsgBox PROMPT
            hwnd = Shell("notepad.exe " & ThisWorkbook.Path & "\MultipleInstancesUserInstructions.txt", vbNormalFocus)
            Exit Function

        End If

    Loop

    Exit Function

MultipleExcelInstances_EH:

    MsgBox "Error [" & Err.Number & "] in " & PROC_NAME & ": " & Err.Description

    Exit Function

End Function

Here are the instructions I open in notepad (referenced in the code above as “MultipleInstancesUserInstructions.txt”):

MULTIPLE INSTANCES OF EXCEL
===========================

XYZ Reporting using Essbase will not function properly if you have multiple instances of Excel open.

If you received this message but you do NOT not see more than one instance of Excel, follow the instructions below.

1. Close Excel.

2. Click Start->Run and type in "taskmgr.exe" to run Task Manager.

3. Select the "Processes" tab and click on "Image Name" to sort by name.

4. click on each instance, if any, of "EXCEL.EXE" and choose "End Process."

5. Rerun Excel, and retry XYZ Reports. If you still have a problem, contact support.

Tagged as: error, Essbase, Excel, Hyperion Essbase, Oracle