Iterating through Cells .Net/Excel 2003
 

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

Iterating through Cells .Net/Excel 2003
 

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.

Iterating through Cells .Net/Excel 2003
 

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

--
Message posted from http://www.ExcelForum.com

Iterating through Cells .Net/Excel 2003
 

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...b;en-us;328347

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

--

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

Iterating through Cells .Net/Excel 2003
 

Hi Wei-Dong Xu,

Thanks for the help. You suggestion worked perfectly.

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

Iterating through Cells .Net/Excel 2003
 

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

thanks.

Rick
"onedaywhen" wrote in message
om...
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...b;en-us;328347

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

--

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

Iterating through Cells .Net/Excel 2003
 

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.