You may receive a "Member Not Found" error message when you use a For Each statement on an Excel collection in Visual Basic .NET or in Visual C# .NET (328347)

MORE INFORMATION

Steps to reproduce the problem

1. Start Microsoft Visual Studio .NET.
2. On the File menu, click New and then click Project.
3. Under Visual Basic Projects, click to select Windows Application.
   
   By default, Form1 is created.
4. Add a reference to the Microsoft Excel Object Library. You can do this by following these steps:
   1. On the Project menu, click Add Reference.
   2. On the COM tab, locate Microsoft Excel Object Library and then click Select.
      
      Note Microsoft Office 2003 includes Primary Interop Assemblies (PIAs). Microsoft Office XP does not include PIAs, but they can be downloaded. For additional information about Office XP PIAs, click the following article number to view the article in the Microsoft Knowledge Base:

      328912 Microsoft Office XP primary interop assemblies (PIAs) are available for download

   3. Click OK in the Add References dialog box.
5. On the View menu, click to select Toolbox and then add a button to Form1.
6. Double-click Button1 to display the code window for Form1 and then add the following code to the Click event handler of the button:

   Dim oApp As New Excel.Application()
   oApp.Visible = True
   oApp.UserControl = True
   
   Dim oSheet As Excel.Worksheet
   oSheet = oApp.Workbooks.Add.Worksheets.Item(1)
   
   Dim oCell As Excel.Range
   For Each oCell In oSheet.Range("A1:B5")
       oCell.Value = "test"
   Next
   					

7. Add the following code to the top of the form module:

   Imports Excel = Microsoft.Office.Interop.Excel
   					

8. Press F5 to run the program.
9. As soon as Form1 loads, click Button1.
   
   You receive the error message:
   Member not found
   that is described in the "Symptoms" section of this article.

Workaround

To work around the error, you can replace the code in the Click event handler of the button with the following code:

Dim oApp As New Excel.Application()
oApp.Visible = True
oApp.UserControl = True

Dim oSheet As Excel.Worksheet
oSheet = oApp.Workbooks.Add.Worksheets.Item(1)

Dim oRng As Excel.Range
Dim nRows As Long, nCols As Long
oRng = oSheet.Range("A1:B5")
For nRows = 1 To oRng.Rows.Count
    For nCols = 1 To oRng.Columns.Count
        oRng.Cells(nRows, nCols).Value = "test"
    Next
Next
				

Then, run the program again. When you click Button1, cells A1:B5 in the new worksheet are populated with text as expected.