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.