Getting data from a closed wbook
in [Programming]
|
Prev: Counting records with ADO
Next: Microsoft.Office.Interop.Excel myexcel = new EXCEL.Application()
From: Geoff K on 4 Oct 2009 18:00 The current project, which uses open wbks, solves all those issues satisfactorily - even dealing with flawed UsedRanges. What I am trying to do is extend the current use of ADO and also work from closed wbks. It saves a great deal of time. And the beauty of using ADO (from a closed wbk) is that I can ignore having to undo hidden rows, autofilters etc etc. In one experiment I hid all data rows and columns, ran the process and it still produced the same final results as if the wbk had been open BUT as I said a great deal quicker. On average using ADO on a folder of closed wbks reduces processing time by half. It is a prize worth pursuing as this application is only part of a wider process. Can you produce a flawed UsedRange wbk? I can't. My only understanding of the phenomenon is they can be caused by "a frequent change of data area, cutting and pasting" but who knows? Geoff "RB Smissaert" wrote: > > hidden rows, hidden columns, autofilters, merged cells, wordwraps, end of > > line characters > > - some even without any field names > > OK, I hadn't tested for all that. > Did you try the latest ADO code I posted? > Can't you produce a demo wb that has (all of) the above problems and make it > fail with ADO code?
From: RB Smissaert on 4 Oct 2009 18:30 I have tried, but not managed to make the ADO method I posted last fail. If there are no fields at all then it will give one row number less, but that makes sense, as it will consider the first row with data the field row. Hiding rows and columns, merging cells, autofilter and linebreaks in cells didn't cause any problem. So, not sure what causes the problem in your wb. RBS "Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message news:A3DB79C2-BCE2-4671-93D4-ED751885C819(a)microsoft.com... >I mentioned in my first post here that I was looking at using a formula to > include MATCH(99^99 or MATCH("ZZZ" etc The idea is to get a row value for > every field, then get the maximum which will give me the last used row and > original record count. > > It coincidently looks similar to Ron de Bruin's code in his Main Program > at > the bottom of the page. What is interesting is how he turns formulae into > values. I insert my formula on my hidden Add-in wsheet. But last night I > was getting stuck on how to convert the results into a value - so that > snippet will be useful. > > But - even this method fails with the largest UsedRange flaw. The wbk > justs > hangs. And even with normal wbks it can be very slow. I have to check > all > fields for end of row because required fields are not always in the same > order and I need th get the original count prior to processing. > > Geoff > > "RB Smissaert" wrote: > >> Did you try the fixed code that works with ADO? >> >> RBS >
From: Geoff K on 4 Oct 2009 18:42 I have been able to create a flawed UsedRange wbk! Not sure I can remember exactly how. < g> I have run the recent ADO on it and the method does not produce the expected result. How can I send the wbk to you? Or, I can try and retrace my steps to replicate the wbk and pass those on. Geoff "RB Smissaert" wrote: > I have tried, but not managed to make the ADO method I posted last fail. > If there are no fields at all then it will give one row number less, but > that makes sense, as it > will consider the first row with data the field row. Hiding rows and > columns, merging cells, autofilter and > linebreaks in cells didn't cause any problem. So, not sure what causes the > problem in your wb. > > RBS > > > > "Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message > news:A3DB79C2-BCE2-4671-93D4-ED751885C819(a)microsoft.com... > >I mentioned in my first post here that I was looking at using a formula to > > include MATCH(99^99 or MATCH("ZZZ" etc The idea is to get a row value for > > every field, then get the maximum which will give me the last used row and > > original record count. > > > > It coincidently looks similar to Ron de Bruin's code in his Main Program > > at > > the bottom of the page. What is interesting is how he turns formulae into > > values. I insert my formula on my hidden Add-in wsheet. But last night I > > was getting stuck on how to convert the results into a value - so that > > snippet will be useful. > > > > But - even this method fails with the largest UsedRange flaw. The wbk > > justs > > hangs. And even with normal wbks it can be very slow. I have to check > > all > > fields for end of row because required fields are not always in the same > > order and I need th get the original count prior to processing. > > > > Geoff > > > > "RB Smissaert" wrote: > > > >> Did you try the fixed code that works with ADO? > >> > >> RBS > > > >
From: RB Smissaert on 4 Oct 2009 18:45 > How can I send the wbk to you? Just zip it and mail it to me. RBS "Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message news:2098D600-0807-4CCA-877C-6E03BCC66A7B(a)microsoft.com... >I have been able to create a flawed UsedRange wbk! Not sure I can remember > exactly how. < g> > > I have run the recent ADO on it and the method does not produce the > expected > result. > > How can I send the wbk to you? Or, I can try and retrace my steps to > replicate the wbk and pass those on. > > Geoff > > "RB Smissaert" wrote: > >> I have tried, but not managed to make the ADO method I posted last fail. >> If there are no fields at all then it will give one row number less, but >> that makes sense, as it >> will consider the first row with data the field row. Hiding rows and >> columns, merging cells, autofilter and >> linebreaks in cells didn't cause any problem. So, not sure what causes >> the >> problem in your wb. >> >> RBS >> >> >> >> "Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message >> news:A3DB79C2-BCE2-4671-93D4-ED751885C819(a)microsoft.com... >> >I mentioned in my first post here that I was looking at using a formula >> >to >> > include MATCH(99^99 or MATCH("ZZZ" etc The idea is to get a row value >> > for >> > every field, then get the maximum which will give me the last used row >> > and >> > original record count. >> > >> > It coincidently looks similar to Ron de Bruin's code in his Main >> > Program >> > at >> > the bottom of the page. What is interesting is how he turns formulae >> > into >> > values. I insert my formula on my hidden Add-in wsheet. But last >> > night I >> > was getting stuck on how to convert the results into a value - so that >> > snippet will be useful. >> > >> > But - even this method fails with the largest UsedRange flaw. The wbk >> > justs >> > hangs. And even with normal wbks it can be very slow. I have to check >> > all >> > fields for end of row because required fields are not always in the >> > same >> > order and I need th get the original count prior to processing. >> > >> > Geoff >> > >> > "RB Smissaert" wrote: >> > >> >> Did you try the fixed code that works with ADO? >> >> >> >> RBS >> > >> >>
From: Geoff K on 4 Oct 2009 18:53
On the way. Geoff "RB Smissaert" wrote: > > How can I send the wbk to you? > Just zip it and mail it to me. > > RBS > > "Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message > news:2098D600-0807-4CCA-877C-6E03BCC66A7B(a)microsoft.com... > >I have been able to create a flawed UsedRange wbk! Not sure I can remember > > exactly how. < g> > > > > I have run the recent ADO on it and the method does not produce the > > expected > > result. > > > > How can I send the wbk to you? Or, I can try and retrace my steps to > > replicate the wbk and pass those on. > > > > Geoff > > > > "RB Smissaert" wrote: > > > >> I have tried, but not managed to make the ADO method I posted last fail. > >> If there are no fields at all then it will give one row number less, but > >> that makes sense, as it > >> will consider the first row with data the field row. Hiding rows and > >> columns, merging cells, autofilter and > >> linebreaks in cells didn't cause any problem. So, not sure what causes > >> the > >> problem in your wb. > >> > >> RBS > >> > >> > >> > >> "Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message > >> news:A3DB79C2-BCE2-4671-93D4-ED751885C819(a)microsoft.com... > >> >I mentioned in my first post here that I was looking at using a formula > >> >to > >> > include MATCH(99^99 or MATCH("ZZZ" etc The idea is to get a row value > >> > for > >> > every field, then get the maximum which will give me the last used row > >> > and > >> > original record count. > >> > > >> > It coincidently looks similar to Ron de Bruin's code in his Main > >> > Program > >> > at > >> > the bottom of the page. What is interesting is how he turns formulae > >> > into > >> > values. I insert my formula on my hidden Add-in wsheet. But last > >> > night I > >> > was getting stuck on how to convert the results into a value - so that > >> > snippet will be useful. > >> > > >> > But - even this method fails with the largest UsedRange flaw. The wbk > >> > justs > >> > hangs. And even with normal wbks it can be very slow. I have to check > >> > all > >> > fields for end of row because required fields are not always in the > >> > same > >> > order and I need th get the original count prior to processing. > >> > > >> > Geoff > >> > > >> > "RB Smissaert" wrote: > >> > > >> >> Did you try the fixed code that works with ADO? > >> >> > >> >> RBS > >> > > >> > >> > > |