stanl
02-26-2012, 12:55 PM
I am working with huge 2007/2010 tabs often containing 90-200,000 rows. I summarize specific columns by date into a db table. The dates, however, are in the format - example:112//2/19 which I convert to 2/19/2012 via a UDF.
The files are dumped daily just adding rows, consolidated monthly, the file basically overwritten. This leaves me with a parsing law of diminishing returns, as I must create a daily lookup table to ensure as I summarize the daily tabs I avoid unnecessary propogation of values, viz: if 2/19/2012 data is already summarized ignore rows with that date.
If you are confused at this point: assume 1-31 March 2012, with 5-10,000 rows added daily. I start parsing on March 2 for March 1 data - 5,000 rows. On March 3 the file contains March1+March2 so let's say it now has 12,000 rows. My parser would then read rows, eliminating all the previously summarized March1 data, then updating the db table with March2 data. So you can see why I used the term 'diminishing returns'.
So, now to the point. Assuming the data is concatenated daily, then the last rows contains the latest dates. So rather than performing a lookup moving from the beginning for n rows, I simply work backward from the last row until I have a successful lookup, then simply stop parsing.
With Excel 2007/2010 I have looked at
row=oWS.Cells.SpecialCells(xlCellTypeLastCell).Row
which appears to be what I want. My question boils down to: does anyone have knowledge or experience where this might return an incorrect value:dunno
Also: I'm open for suggestions for a better mousetrap.
The files are dumped daily just adding rows, consolidated monthly, the file basically overwritten. This leaves me with a parsing law of diminishing returns, as I must create a daily lookup table to ensure as I summarize the daily tabs I avoid unnecessary propogation of values, viz: if 2/19/2012 data is already summarized ignore rows with that date.
If you are confused at this point: assume 1-31 March 2012, with 5-10,000 rows added daily. I start parsing on March 2 for March 1 data - 5,000 rows. On March 3 the file contains March1+March2 so let's say it now has 12,000 rows. My parser would then read rows, eliminating all the previously summarized March1 data, then updating the db table with March2 data. So you can see why I used the term 'diminishing returns'.
So, now to the point. Assuming the data is concatenated daily, then the last rows contains the latest dates. So rather than performing a lookup moving from the beginning for n rows, I simply work backward from the last row until I have a successful lookup, then simply stop parsing.
With Excel 2007/2010 I have looked at
row=oWS.Cells.SpecialCells(xlCellTypeLastCell).Row
which appears to be what I want. My question boils down to: does anyone have knowledge or experience where this might return an incorrect value:dunno
Also: I'm open for suggestions for a better mousetrap.