Iterating through Cells .Net/Excel 2003

[R Kalal]

From VB.Net(2003) I want to be able to iterate through all cells "c", in a
runtime defined range object(excel 2003). However, when the xTest sub is
run, it generates a member not found error on the line( for each c in
objrange.cells).

Any help would be greatly appreciated.



Thanks Rick

declarations

Private objExcel As Microsoft.Office.Interop.Excel.Application

Private objWorkbook As Microsoft.Office.Interop.Excel.Workbook

Private objRange As Microsoft.Office.Interop.Excel.Range



function xTest(strSomeRange as string) as variant

Dim c As object

objExcel = New Microsoft.Office.Interop.Excel.Application

objExcel.Visible = True

objWorkbook = objExcel.Workbooks.Add()

objRange = objWorkbook.ActiveSheet.Range(strSomeRange)



For Each c In objRange.Cells ' this is where the error occurs.

'some code goes here and sets a format based on the cells content

Next c

End Sub

 

[Wei-Dong Xu [MSFT]]

Hi Rick,

Thanks for posting in the community!

From my understanding to this issue, you met the "Member not found" error
when you want to set the cell property in one range with VB.net.

You can convert the Range.Cells type to "Object" so that the variable "c"
in "For each" statement will retrieve the member from Cells very
successfully. I list the codes for you:
'Code begin ----------------------------------------------------
...
Dim c as object
For Each c In CType(objRange.Cells, Object)
c.Value = 1000
Next c
...
'Code end ------------------------------------------------------

Please feel free to let me know if you have any further questions. I am
standing by to be of assistance.

Enjoy a nice day!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[cplater]

shouldnt you just dim C as range ?

... btw, you will find that due to interop this is a very slow way o
accessing cells. I would suggest grabbing the .value to an array an
iterating that. You can also use ADO.Net to access/update sheet dat
using datatables

 

[onedaywhen]

Are you sure its not the following known problem:

Microsoft Knowledge Base Article - 328347
PRB: "Member Not Found" Error Message When You Use a For Each
Statement on an Excel Collection with Visual Basic .NET or Visual C#
..NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;328347

This posting is provided "AS IS" with no warranties, and confers no
rights, but at least I looked in MSDN first.

 

[R Kalal]

Hi Wei-Dong Xu,

Thanks for the help. You suggestion worked perfectly.

Rick

 

[R Kalal]

My issue appears to be related to this error. Thankfully, Wei-Dong Xu's
workaround worked.

thanks.

Rick

Are you sure its not the following known problem:

Microsoft Knowledge Base Article - 328347
PRB: "Member Not Found" Error Message When You Use a For Each
Statement on an Excel Collection with Visual Basic .NET or Visual C#
.NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;328347

This posting is provided "AS IS" with no warranties, and confers no
rights, but at least I looked in MSDN first.

--

(e-mail address removed) (Wei-Dong Xu [MSFT]) wrote in message

Hi Rick,

Thanks for posting in the community!

From my understanding to this issue, you met the "Member not found" error
when you want to set the cell property in one range with VB.net.

You can convert the Range.Cells type to "Object" so that the variable "c"
in "For each" statement will retrieve the member from Cells very
successfully. I list the codes for you:
'Code begin ----------------------------------------------------
..
Dim c as object
For Each c In CType(objRange.Cells, Object)
c.Value = 1000
Next c
..
'Code end ------------------------------------------------------

Please feel free to let me know if you have any further questions. I am
standing by to be of assistance.

Enjoy a nice day!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no

rights.

 

[Wei-Dong Xu [MSFT]]

Hi Rick,

Thank you for replying!

My pleasure to be some of service. Enjoy a nice weekend!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.