Automation Error Using Excel Object in Visual Basic Procedure

This article was previously published under Q134835
This article has been archived. It is offered "as is" and will no longer be updated.
When you use a Visual Basic application to create an OLE Automation objectusing Microsoft Excel, you may receive the following error message when youattempt to access the object:
OLE Automation error
This problem occurs when you access a Microsoft Excel OLE Automation objectin a Visual Basic procedure while Microsoft Excel is closed. If, forexample, you create a reference for a worksheet object using theCreateObject function, and you create a reference for another MicrosoftExcel object using the GetObject function in your procedure, if you thenset the Microsoft Excel worksheet object (Excel.Sheet) equal to nothing,you receive the OLE Automation error message if you then try to access theother object.

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

For example, you receive this error message when you run the following codein 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 thatfollows in the procedure attempts to access the Microsoft Excel applicationobject again.

Note that in the above example, if you set the Microsoft Excel applicationobject equal to nothing (Set xlApp = Nothing), you do not receive an errormessage if you then access the Microsoft Excel worksheet object (xlSheet)in the procedure. Additionally, you do not receive this error message ifMicrosoft Excel is running when you run this macro because, in this case,the CreateObject function starts another instance of Microsoft Excel.
To avoid this behavior in a Visual Basic procedure, do not set the valueof a Microsoft Excel object that was created using the CreateObjectfunction equal to Nothing until you are done accessing all Microsoft ExcelOLE Automation objects in the procedure.
5.00c XL

Article ID: 134835 - Last Review: 12/04/2015 11:50:17 - Revision: 1.5

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

  • kbnosurvey kbarchive kberrmsg kbinterop kbprogramming kbdtacode KB134835