On Apr 14, 2006, at 4:17 PM, Kevin Windham wrote:
On Apr 14, 2006, at 3:00 PM, Jeremy Clifton wrote:
I'm trying to loop through all cells in a single column in an
Excel spreadsheed.
...
From looking at the VBA reference, it *looks* as if I can do the
following to loop through all the cells in column C:
-------
Dim excel as ExcelApplication
Dim wbook as ExcelWorkbook
Dim cell as ExcelRange
Dim formulas() as String
' Doing some stuff here to open the workbook ...
For Each cell in wbook.ActiveSheet.Columns("C").Cells
If cell.HasFormula Then
formulas.Append(cell.Formula)
End If
Next
Try something like wbook.ActiveSheet.Cells(row,column) where row
and column are numbers. So for the "C" column you could do a for
loop like
for i = 1 to 100 //or whatever you want to end at...
cell = wbook.ActiveSheet.Cells(i,3)
If cell.HasFormula Then
formulas.Append(cell.Formula)
End If
Next
That's actually what I've ended up doing. My only problem with that
(and thus why I called it 'less elegant' is that the number of of
rows in each spreadsheet varies (the user actually chooses which
spreadsheet to process). I only have about 10 of them, out of several
hundred.
I could guess the largest possible value (the largest one I have has
3680 rows) and set the max value of i in the For loop. I was hoping
to avoid a call from the client wondering why the program wasn't
processing all the lines of the spreadsheet (in the case that one of
the ones they have has more rows, or a later spreadsheet is added
that has more rows). The down side to that (and really the other
approach) is that if the spreadsheet being processed has only 500
rows with data ... then I waste time looping through another 3000+
empty cells ... and this seems to take a l-o-n-g time.
Yes, their data storage method is pretty ugly.
So ... actually, if anybody knows of a method to only loop through
the cells with data in them ... that would be appreciated. However,
my guess is that there is no way to do that, based on what I've seen
in the VBA reference and in my Google research.
Jeremy
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
