Knowledge Base

Excel 2007 continues to run after you use a COM add-in and then try to exit Excel 2007

Article ID: 931399

Article Last Modified on 10/15/2007

APPLIES TO

Microsoft Office Excel 2007

SYMPTOMS

You use a custom Component Object Model (COM) add-in with Microsoft Office Excel 2007. Then, you try to exit Excel 2007. When you do this, the Excel 2007 window is hidden. However, Excel 2007 continues to run.

CAUSE

This problem may occur if the COM add-in is made visible to you because Excel 2007 was started for OLE or for remote Automation.

By design, Excel 2007 remains loaded until all external connections have been released. If you manually close the Excel 2007 window, and if an external OLE host or an Automation host still has a reference count to the application, the Excel 2007 window is hidden. However, Excel 2007 continues to run.

This behavior follows the correct COM rules about object lifetime. However, an internal event may be triggered during this process that can cause Excel 2007 to start a shutdown. When the shutdown is started, all COM add-ins are unloaded. Excel then determines that the application will not be closed because the external references are not released. Therefore, COM add-ins may be unloaded before Excel 2007 is ended.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

When you work with an Excel application, you can extend application functionality by using a custom COM add-in. The add-in can be set to load at startup. The add-in remains loaded until the application is shut down.

However, if Excel is started for OLE or for remote Automation, and if the application is made visible to you, the Excel window is hidden only when you try to close the Excel window. The application remains running.

When this behavior occurs, the COM add-in is unloaded as if the application were shut down. The OnBeginShutdown event and the OnDisconnection event are called. Additionally, the add-in is removed from memory. If the application is made visible again, the COM add-in is no longer loaded. The menu items or the toolbar items that were added by the COM add-in may no longer be available. You may be unable to use the COM add-in again in that instance of Excel.

Steps to reproduce the problem

This procedure uses a Microsoft Visual Basic 6.0 add-in and Windows Internet Explorer to reproduce this problem. However, this problem can occur in many other add-ins, other OLE clients, and other Automation clients. The effect of the problem may change and will depend on the design and implementation of the add-in.

Step 1: Create test workbooks in Excel 2007

Start Excel 2007, and then create three new workbooks.
Add data to the first two workbooks, and then save the workbooks as TestBook1.xls and TestBook2.xls.

Note Save the workbooks to a location that you can access easily from Windows Explorer.

Open the third workbook.
On the Insert tab, click Hyperlink.
In the Insert Hyperlink dialog box, locate TestBook1.xls, and then click OK to add a link to TestBook1.xls.
Click the Microsoft Office Button, click Save As, and then click Other Formats.
In the File name box, type TestPage.htm.
In the Save as type list, click Web page (*.htm; *.html).
Save TestPage.htm in the same location in which you saved TestBook1.xls and TestBook2.xls.
Close all the documents, and then exit Excel.

Step 2: Create a Visual Basic COM add-in for Excel

Start Visual Basic 6.0.
In the New Project dialog box, click Addin, and then click Open.

The "Connect" designer class and the "frmAddin" form are added automatically.
In the Properties pane, type MyExcelAddin in the Name field.
In the Project pane, expand Designers, and then double-click Connect (Connect).
In the MyExcelAddin - Connect (AddInDesigner) dialog box, click Microsoft Excel in the Application list.
In the Initial Load Behavior list, click Startup.
In the Project pane, expand Forms, right-click frmAddin, and then click Remove frmAddIn.
In the Project window, right-click Connect, and then click View Code.

In the My ExcelAddIn - Connect (Code) pane, remove all the code, and then replace that code with the following code:

Option Explicit

Dim WithEvents m_oCommandBarButton As Office.CommandBarButton

Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    ByVal AddInInst As Object, custom() As Variant)
    Dim oCommandBar As Office.CommandBar
    Dim oButton As Office.CommandBarButton
    On Error Resume Next
    ' Get the Standard toolbar for Excel to add the button.
    Set oCommandBar = Application.CommandBars.Item("Standard")
    ' Try to connect to an existing item.
    Set oButton = oCommandBar.Controls("My Button")
    ' If this fails, you do not have one yet. Try to make one.
    If Err Then
        Err.Clear
        Set oButton = oCommandBar.Controls.Add(1)
        If Not Err Then
            oButton.Caption = "My Button"
            oButton.Style = msoButtonCaption
            oButton.Tag = "MyButtonTag"
        End If
    End If
    'Display an error if button creation fails.
    If Err Or oButton Is Nothing Then
        MsgBox "Error loading add-in: " & Err.Description, _
            vbCritical Or vbMsgBoxSetForeground
    Else ' Otherwise, you set the button to handle the events.
        Set m_oCommandBarButton = oButton
    End If
End Sub

Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode _
    As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
    On Error Resume Next
    MsgBox "The Add-In is being disconnected now.", _
        vbInformation Or vbMsgBoxSetForeground
    Set m_oCommandBarButton = Nothing
End Sub

Private Sub m_oCommandBarButton_Click(ByVal Ctrl As _
    Office.CommandBarButton, CancelDefault As Boolean)
    On Error Resume Next
    MsgBox "This event was handled by the COM Add-In.", _
        vbInformation Or vbMsgBoxSetForeground
End Sub

On the File menu, click Save Project.
On the File menu, click Make MyExcelAddin.dll.

Visual Basic registers the add-in.

Step 3: Run the test page to reproduce the problem

Close all applications. Make sure that Excel is not running.
Open TestPage.htm in Internet Explorer.
Click the hyperlink.

By default, in-place activation for Excel for OLE starts, and TestBook1.xls opens in the Web browser. If TestBook1.xls does not open in the Web browser, view the following article in the Microsoft Knowledge Base for more information about how to reconfigure Internet Explorer to start .xls files in place:

259970 In-place activating document servers in Internet Explorer
Continue to let Internet Explorer run, find TestBook2.xls in Windows Explorer, and then double-click TestBook2.xls to open this file outside the Web browser.
On the Standard toolbar, click My Button.

You receive a message from the add-in that tells you that the event was handled.
In Internet Explorer, click Back to move away from the embedded Excel workbook.

The workbook window is closed. However, the reference to Excel is kept. Internet Explorer caches the object.
Switch back to Excel, and then close TestBook2.xls.
On the File menu, click Exit.

You receive a message from the add-in that the add-in is being unloaded. Excel hides the Excel main window. However, Excel continues to run.
Use Windows Explorer to locateTestBook2.xls, and then double-click TestBook2.xls to reopen the file in Excel.
On the Standard toolbar, click My Button.

Nothing occurs because the add-in was previously unloaded.

Additional query words: xl2007

Keywords: kbtshoot kbaddin kbprb kbexpertisebeginner KB931399

Microsoft KB Archive/931399

Contents