Microsoft KB Archive/199219

= XL2000: Automation Doesn't Release Excel Object from Memory =

Article ID: 199219

Article Last Modified on 10/7/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q199219



SYMPTOMS
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.



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



WORKAROUND
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:

myExcel.Workbooks(1).Worksheets(1).Cells(1,1).Value = "Hello" myExcel.Workbooks(1).Worksheets(1).Cells(2,1).Value = "World"

Instead of:

With myExcel.Workbooks(1).Worksheets(1) .Cells(1,1).Value = "Hello" .Cells(2,1).Value = "World" End With



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

