Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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

  1. Start a new Standard EXE project in Visual Basic. Form1 is created by default.

  2. On the Project menu, click References, and then check the Object Library for the version of Excel that you intend to automate.

  3. Place a CommandButton control on Form1.

  4. 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
    
  5. On the Run menu, click Start, or press F5 to start the program.

  6. Click the CommandButton control. No error occurs. However, a reference to Excel has been created and has not been released.

  7. 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.

  8. 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"
    
  9. 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
 

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×