XL2000: Automation Doesn't Release Excel Object from Memory

• Microsoft Excel 2000

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" 

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

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

REFERENCES

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.

Additional query words: OFF2000 XL2000

Keywords : kbdta xlvbainfo
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb