Invisible Instances of Office Applications: Who is to Blame?

A couple of recent articles and videos in the Access community got me thinking about the somewhat related topic of COM automation.

Forcibly Shutdown Access

This article discusses several different ways of forcibly shutting down Access if necessary when problems occur. Warnings are given about the risks of doing so.

Isladogs on AccessColin Riddington

Hanging instance of Access after closing a database

You close a database and supposedly terminate Access. After that, databases can no longer be opened by clicking on the file in Windows Ex...

AccessForeverKarl Donaubauer

Hanging Office Applications

The above articles and video deal specifically with the Access executable failing to terminate properly due to a variety of different Access bugs.  However, similar behavior can be seen with other Office applications, including Word, Excel, and Outlook.  And while those situations can also be caused by bugs in the respective applications, they can also be a side effect of sloppy COM automation programming in VBA.

COM Automation

By far, the most common usage of "COM automation" is to interact with one Office application (such as Excel) from another Office application (such as Access) from within VBA.

High Level Overview of COM

This whirlwind tour of the Component Object Model technology will jump start your journey of automating Excel from VBA in Access.

No Longer SetMike Wolfe

The key line from the above article for our purposes today is this one:

As a VBA developer, you have exactly one COM responsibility: clean up after yourself.

If you don't clean up the object references to other Office applications, those applications can't shut down.  

Placing the Blame for Hanging Office Applications

One of the easiest ways to identify who is responsible for a hanging Office application is to look at its command line.

If the command line includes the arguments /automation -Embedding, then you know that instance of the application was created via COM.

The command line column is not displayed by default in Task Manager.  To view it, follow these steps:

1. Open Task Manager
2. Go to the Details tab
3. Right-click an existing column header then choose Select columns
4. Scroll down and put a check mark next to [√] Command line
5. Click [OK]

Once you've displayed the "Command line" column, go to the hanging Office application and look for the words /automation -Embedding:

Reproducing a Hanging Excel Instance

To better understand how COM automation works at the process level, let's look at some sample code.

!!!WARNING!!! The sample code below includes a line that will close all open instances of Excel without even prompting you to save any pending changes.  I strongly recommend you close all open instances of Excel before continuing.

The sample code below uses late-binding to create an instance of Excel.  It has message box calls placed throughout to inform you of what's going on and introduce natural stopping points:

Sub CreateNewExcelProcessViaAutomation()
    Const LeaveExcelHanging As Boolean = False

    Dim appExcel As Object
    Set appExcel = CreateObject("Excel.Application")
    
    MsgBox "Invisible copy of Excel running via COM automation" & _
           vbNewLine & vbNewLine & _
           "excel.exe /automation -Embedding"
    
    appExcel.Visible = True
    MsgBox "Existing Excel process now visible"
    
    'If we don't set appExcel to Nothing, then excel.exe will hang indefinitely
    If LeaveExcelHanging Then Exit Sub
    
    Set appExcel = Nothing
    MsgBox "Existing Excel process terminating"
    
End Sub

Now, sometimes you don't want to create a new instance of the other Office application and instead you just want to grab an existing instance.  You can do that using the GetObject() function:

Sub GrabExistingExcelInstance()
    Shell "Excel.exe", vbNormalNoFocus
    MsgBox "Visible copy of Excel running as normal"
    
    'Grab the running copy of Excel
    Dim appExcel As Object
    Set appExcel = GetObject(, "Excel.Application")
    MsgBox "appExcel refers to running copy of Excel"
    
    appExcel.Visible = False
    MsgBox "Excel temporarily hidden"
    
    appExcel.Visible = True
    MsgBox "Excel now visible again"
    
    Set appExcel = Nothing
    MsgBox "Excel still running despite being disconnected from appExcel"
    
    '!!! Be careful testing this next line !!!
    'It will close all open instances of Excel instantly,
    '   not just the one we started above
    Shell "taskkill /im Excel.exe"
    MsgBox "All existing Excel processes now terminated."
    
End Sub

More Info on COM Programming

It took all the restraint I had not to turn this article into yet another COM explainer.  That said, if you would like to learn more about COM programming in VBA, check out this series of articles I wrote on the topic:

COM - No Longer Set

The Component Object Model (COM) is the language-independent binary technology that underpins the entire Windows ecosystem. These articles explore the world of COM in varying levels of depth.

No Longer SetMike Wolfe

Acknowledgements

• Cover image generated by DALL-E-3