Microsoft KB Archive/178510

= Excel automation fails second time code runs =

Article ID: 178510

Article Last Modified on 5/14/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95a
 * Microsoft Excel 95 Standard Edition
 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Standard Edition
 * Microsoft Visual Basic for Applications 5.0
 * Microsoft Office XP Developer Edition
 * Microsoft Office 2000 Developer Edition

-



This article was previously published under Q178510



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 ' ' 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.</li> Click the CommandButton control. No error occurs. However, a reference to Excel has been created and has not been released.</li> 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.</li>  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" </li> Run the program again. Notice that you can run the code multiple times without receiving an error message.</li></ol>

<div class="references_section">