Automation Error Using Excel Object in Visual Basic Procedure

Article translations Article translations
Article ID: 134835 - View products that this article applies to.
This article was previously published under Q134835
Expand all | Collapse all

SYMPTOMS

When you use a Visual Basic application to create an OLE Automation object using Microsoft Excel, you may receive the following error message when you attempt to access the object:
OLE Automation error

CAUSE

This problem occurs when you access a Microsoft Excel OLE Automation object in a Visual Basic procedure while Microsoft Excel is closed. If, for example, you create a reference for a worksheet object using the CreateObject function, and you create a reference for another Microsoft Excel object using the GetObject function in your procedure, if you then set the Microsoft Excel worksheet object (Excel.Sheet) equal to nothing, you receive the OLE Automation error message if you then try to access the other object.

This problem occurs because setting a Microsoft Excel object that was created using the CreateObject function equal to Nothing closes Microsoft Excel, even if your Visual Basic procedure still has a reference to another Microsoft Excel object.

For example, you receive this error message when you run the following code in Microsoft Visual Basic:
   Dim xlSheet As Object
   Dim xlApp As Object
   Set xlSheet = CreateObject("Excel.Sheet")
   MsgBox xlSheet.Application.Name
   Set xlApp = GetObject(, "Excel.Application")
   MsgBox xlApp.Name
   Set xlSheet = Nothing
   MsgBox xlApp.Name
				
You receive this error message because the statement "Set xlSheet = Nothing" closes Microsoft Excel, and the "MsgBox xlApp.Name" statement that follows in the procedure attempts to access the Microsoft Excel application object again.

Note that in the above example, if you set the Microsoft Excel application object equal to nothing (Set xlApp = Nothing), you do not receive an error message if you then access the Microsoft Excel worksheet object (xlSheet) in the procedure. Additionally, you do not receive this error message if Microsoft Excel is running when you run this macro because, in this case, the CreateObject function starts another instance of Microsoft Excel.

RESOLUTION

To avoid this behavior in a Visual Basic procedure, do not set the value of a Microsoft Excel object that was created using the CreateObject function equal to Nothing until you are done accessing all Microsoft Excel OLE Automation objects in the procedure.

Properties

Article ID: 134835 - Last Review: October 11, 2006 - Revision: 1.5
APPLIES TO
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0c
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Visual Basic 3.0 Professional Edition
  • Microsoft Visual Basic 3.0 Professional Edition
Keywords: 
kberrmsg kbinterop kbprogramming kbdtacode KB134835

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com