Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterating files in folder | Excel Worksheet Functions | |||
Iterating through string in a cell | Excel Discussion (Misc queries) | |||
iterating checkboxes | Excel Discussion (Misc queries) | |||
iterating through all properties of an object | Excel Programming | |||
Iterating through row cells on column Match, help please. | Excel Programming |