Excel automation fails second time code runs

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: In Microsoft Excel 95, you receive one of the following error messages:

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.

To resolve this problem, modify the code so each call to an Excel object, method, or property is qualified with the appropriate object variable.

This behavior is by design.

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.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base: