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

Article translations Article translations
Article ID: 328347 - View products that this article applies to.
This article was previously published under Q328347
Expand all | Collapse all

On This Page

SYMPTOMS

When you use a Visual Basic .NET For Each..Next statement or a Visual C# .NET foreach statement to iterate an Excel collection, such as a Range or a Windows collection, you receive the following error message:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll Additional information: Member not found.

RESOLUTION

To resolve this problem, you can use a Visual Basic For...Next statement or a Visual C# for statement instead. The "More Information" section of this article provides an example.

STATUS

Microsoft is researching this problem and will post more information in this article when the information becomes available.

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.

Properties

Article ID: 328347 - Last Review: August 27, 2004 - Revision: 5.4
APPLIES TO
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
Keywords: 
kbpia kbautomation kbprb KB328347

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com