XL2000: Automation Doesn't Release Excel Object from Memory

When you run a macro that uses automation to create a Microsoft Excel object (instance), the Excel object does not exit from memory when you specify. If you create another Excel object after quitting the first, a second instance is in memory.

This problem occurs when your macro uses a With statement that refers to the automation object.

When you create an Excel automation object using the CreateObject function, the automation object remains in memory until you set it equal to the keyword Nothing. However, when you reference the object using a With statement, the object remains in memory until the subroutine ends. In order to control the release of the Excel automation object from memory, do not use the With statement when referring to the Excel object. Instead, explicitly refer to the object by its variable name. For example, with an Excel automation object named myExcel, the following statements illustrate the preferred way to refer to the automation object:

Use this:

Instead of:

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

For more information about the CreateObject function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type CreateObject function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.