General: Automation object doesn't close automatically

    When you use Automation to create a new instance of Word or Excel, setting the object variable to Nothing in the end will not close the new instance automatically.  On top of this, unless you're explicitly changing the Visible property of the new instance to True,  multiple runs of such code will keep on creating new hidden instances of the application till (you guessed it!) you run short of resources. (Such hidden instances can be detected by using the Task Manager under NT or the Task List under Win95)

Try this code as an example:

    Dim objXL As Object
    Set objXL = CreateObject("Excel.Application")

    'your fancy automation code here

    objXL.Visible = True
    Set objXL = Nothing

To close these new instances, you must use the Quit method of the application object.  So the same code should  look like:

    Dim objXL As Object
    Set objXL = CreateObject("Excel.Application")

    'your fancy automation code here

    objXL.Quit
    Set objXL = Nothing

    If the Object's UserControl  property is false, and the referring variable goes out scope, then the application will quit.  This property's setting is what makes the object stay open.

Addition Information

   Here are some useful articles from MSDN that provide additional information on the lifetimes of Automation Objects.

    Managing Object Lifetimes in OLE Automation (by Douglas Hodges)

    Using Microsoft OLE Automation Servers to Develop Solutions (by Ken Lassesen)

    147816: ACC: Using Microsoft Access as an Automation Server

From the above mentioned PSS Knowledge Base article;

UserControl Property

The UserControl property is always read-only; therefore, you cannot set it using Automation. However, the UserControl property can change automatically if a user intervenes while your Automation code is idle. For example, the UserControl property is changed to False when the following events occur:

 

1. The user creates an instance of Microsoft Access, which sets the UserControl property to True.
2. You run Automation code in the controller application, which uses the GetObject() function to activate the previously opened instance of Microsoft Access. The object variable that you use for the instance is a Public or module-level variable.
3. The user restores Microsoft Access using the Windows taskbar (or Task List in Windows NT).
4. The user tries to close Microsoft Access by clicking the Close box. The instance does not close as expected because the Automation controller has a Public or module-level object variable referring to that instance of Microsoft Access. Instead, the instance is minimized, which sets the UserControl and Visible properties to False.

Similarly, the UserControl property is changed to True if the following events occur:

1. You create a new instance of Microsoft Access using Automation. The UserControl property is False. The Visible property is also False; therefore, the instance is minimized.
2. The user restores the instance using the Windows taskbar (or Task List in Windows NT). Or, you call the ShowWindow() API function in Visual Basic to restore the instance using code. In both cases, the UserControl and Visible properties are changed to True. 

If the UserControl property is True, it can affect your ability to control the on-screen behavior of Microsoft Access. Specifically, you should watch out for the following limitations:

• You may receive an error message in your Automation code when you try to set the Visible property to True; the Visible property is read-only when the UserControl property is True.
• You cannot trap or suppress an error message generated by Microsoft Access as the OLE Server application. If you execute a bad command, such as trying to open a form that does not exist in the current database, an error message is displayed.
• An instance of Microsoft Access does not close automatically when the object variable referring to the instance (objAccess) is set to Nothing or it loses scope.