Symptoms
While running code that uses Automation to control Microsoft Excel, one of the following errors may occur: In Microsoft Excel 97 and in later versions of Excel, you receive one of the following error message:
Error message 1
Run-time error '1004': Method '<name of method>' of object '_Global' failed
Error message 2
Application-defined or object-defined error
In Microsoft Excel 95, you receive one of the following error messages:
Error message 1
Run-time error '-2147023174' OLE Automation error
Error message 2
Run-time error '462': The remote server machine does not exist or is unavailable.
Cause
Visual Basic has established a reference to Excel because of a line of code that calls an Excel object, method, or property without qualifying the element with an Excel object variable. Visual Basic does not release this reference until you end the program. This errant reference interferes with automation code when the code is run more than one time.
Resolution
To resolve this problem, modify the code so each call to an Excel object, method, or property is qualified with the appropriate object variable.
Status
This behavior is by design.
More Information
To automate Microsoft Excel, you establish an object variable that usually refers to the Excel Application object or the Excel Workbook object. Other object variables can then be set to refer to a Worksheet, a Range, or other objects in the Microsoft Excel object model. When you write code to use an Excel object, method, or property, you should always precede the call with the appropriate object variable. If you do not, Visual Basic establishes its own reference to Excel. This reference might cause problems when you try to run the automation code multiple times. Note that even if the line of code begins with the object variable, a call may be made to an Excel object, method, or property in the middle of the line of code that is not preceded with an object variable. The following steps illustrate how to reproduce this issue and how to correct the issue.
Steps to reproduce the behavior
-
Start a new Standard EXE project in Visual Basic. Form1 is created by default.
-
On the Project menu, click References, and then check the Object Library for the version of Excel that you intend to automate.
-
Place a CommandButton control on Form1.
-
Copy the following code example to the Code Window of Form1.
Option Explicit Private Sub Command1_Click() Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets("Sheet1") xlSheet.Range(Cells(1, 1), Cells(10, 2)).Value = "Hello" xlBook.Saved = True Set xlSheet = Nothing Set xlBook = Nothing xlApp.Quit Set xlApp = Nothing End Sub -
On the Run menu, click Start, or press F5 to start the program.
-
Click the CommandButton control. No error occurs. However, a reference to Excel has been created and has not been released.
-
Click the CommandButton control again. Notice that you receive one of the error messages that are discussed in the "Symptoms" section. Note The error message occurs because the code refers to the method of the cell without preceding the call with the xlSheet object variable.
-
Stop the project and change the following line of code:
xlSheet.Range(Cells(1,1),Cells(10,2)).Value = "Hello"
Change the line of code to resemble the following line of code.
xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(10,2)).Value = "Hello"
-
Run the program again. Notice that you can run the code multiple times without receiving an error message.
References
189618 You may receive the "Run-time error '-2147023174' (800706ba)" error message or the "Run-time error '462'" when you run Visual Basic code that uses Automation to control Word
