XL2000: Automation Doesn't Release Excel Object from Memory (199219)
The information in this article applies to:
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
STATUSMicrosoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
REFERENCESFor 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.
| Modification Type: | Major | Last Reviewed: | 10/7/2003 |
|---|
| Keywords: | kbprb KB199219 |
|---|
|