Getting data from a closed wbook
in [Programming]
|
Prev: Counting records with ADO
Next: Microsoft.Office.Interop.Excel myexcel = new EXCEL.Application()
From: RB Smissaert on 4 Oct 2009 12:18 It works fine with me. Could you mail me that workbook that gives you the wrong answer? RBS "Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message news:6F174DB2-DB9D-44EF-BB8E-02B32CA13423(a)microsoft.com... > Hi > > I was just about to post the same thing when I spotted your reply. > > It was easy enough to transpose and add 1 for the zero base. > > However the ADO function returns me once more to the start position of > mislaigned UsedRanges. On the bloated wbk it returned the last row as > 50918 > and not the real 98. > > I have been here before. > > MichDenis in another post some way back now supplied a link > http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid > the > pitfalls of flawed UsedRanges but is slow. > > This is frustrating because the incidence of flawed UsedRanges is only > about > 2 wbks in 500. But because of the risk, I have to use the slow method on > every wbook. It would be great if I could detect a flawed UsedRange and > run > the 2 recordset method on that wbk only. On the rest of the wbks I could > use > SELECT COUNT(*) etc. > > FWIW I don't believe SELECT COUNT(*) does any counting at all because it > is > so blisteringly quick. I think instead it probably uses the UsedRange > last > row or something like it. Unfortunately a null is a record to SQL so if > the > wbk has been saved with a flawed UsedRange that is what it uses. > > So I am right back to square 1. If only I could detect a flawed UsedRange > in a closed wbk……… > > Geoff > > > "RB Smissaert" wrote: > >> That code wasn't tested and indeed it is no good at all, mainly because I >> didn't consider the fact >> that an array produced by rs.GetArray is transposed. >> Shortly after I posted better code (via a phone), but it didn't come >> through. >> Try this code instead: >
From: Geoff K on 4 Oct 2009 14:18 I agree, SELECT COUNT(*), RecordCount, GetRows all work fine when the UsedRange reflects the real data range. Excel4Macros don't work properly because the code just hangs. All the above fail to return correct results whenever a wbk has been saved with a UsedRange flaw. The only method which does work is the 2 recordset I mentioned earlier but that is very slow. Unfortunately I am not able to supply the 2 wbks with known UsedRange flaws because of Data Protection. If they did not contain details of names, jobs, addresses and telephone numbers you would be very welcome to have a look. And of course I cannot delete the data as that would reset the UsedRange. If you can think of a way to create a wbk with an incorrect UsedRange and employ any of the above methods then you would make the same observations, I am certain. Unfortunately I have no control over theses wbks which are supplied from outside sources. The standard of presentation is appalling - hidden rows, hidden columns, autofilters, merged cells, wordwraps, end of line characters - some even without any field names - and of course some with a flawed UsedRange. Geoff "RB Smissaert" wrote: > It works fine with me. > Could you mail me that workbook that gives you the wrong answer? > > RBS > > > "Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message > news:6F174DB2-DB9D-44EF-BB8E-02B32CA13423(a)microsoft.com... > > Hi > > > > I was just about to post the same thing when I spotted your reply. > > > > It was easy enough to transpose and add 1 for the zero base. > > > > However the ADO function returns me once more to the start position of > > mislaigned UsedRanges. On the bloated wbk it returned the last row as > > 50918 > > and not the real 98. > > > > I have been here before. > > > > MichDenis in another post some way back now supplied a link > > http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid > > the > > pitfalls of flawed UsedRanges but is slow. > > > > This is frustrating because the incidence of flawed UsedRanges is only > > about > > 2 wbks in 500. But because of the risk, I have to use the slow method on > > every wbook. It would be great if I could detect a flawed UsedRange and > > run > > the 2 recordset method on that wbk only. On the rest of the wbks I could > > use > > SELECT COUNT(*) etc. > > > > FWIW I don't believe SELECT COUNT(*) does any counting at all because it > > is > > so blisteringly quick. I think instead it probably uses the UsedRange > > last > > row or something like it. Unfortunately a null is a record to SQL so if > > the > > wbk has been saved with a flawed UsedRange that is what it uses. > > > > So I am right back to square 1. If only I could detect a flawed UsedRange > > in a closed wbk……… > > > > Geoff > > > > > > "RB Smissaert" wrote: > > > >> That code wasn't tested and indeed it is no good at all, mainly because I > >> didn't consider the fact > >> that an array produced by rs.GetArray is transposed. > >> Shortly after I posted better code (via a phone), but it didn't come > >> through. > >> Try this code instead: > > > >
From: RB Smissaert on 4 Oct 2009 15:20 > 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? RBS "Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message news:7462AF0C-A34D-4E39-B0AD-0295F4B419AE(a)microsoft.com... >I agree, SELECT COUNT(*), RecordCount, GetRows all work fine when the > UsedRange reflects the real data range. Excel4Macros don't work properly > because the code just hangs. > > All the above fail to return correct results whenever a wbk has been saved > with a UsedRange flaw. The only method which does work is the 2 recordset > I > mentioned earlier but that is very slow. > > Unfortunately I am not able to supply the 2 wbks with known UsedRange > flaws > because of Data Protection. If they did not contain details of names, > jobs, > addresses and telephone numbers you would be very welcome to have a look. > And of course I cannot delete the data as that would reset the UsedRange. > > If you can think of a way to create a wbk with an incorrect UsedRange and > employ any of the above methods then you would make the same observations, > I > am certain. > > Unfortunately I have no control over theses wbks which are supplied from > outside sources. The standard of presentation is appalling - hidden rows, > hidden columns, autofilters, merged cells, wordwraps, end of line > characters > - some even without any field names - and of course some with a flawed > UsedRange. > > Geoff > > "RB Smissaert" wrote: > >> It works fine with me. >> Could you mail me that workbook that gives you the wrong answer? >> >> RBS >> >> >> "Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message >> news:6F174DB2-DB9D-44EF-BB8E-02B32CA13423(a)microsoft.com... >> > Hi >> > >> > I was just about to post the same thing when I spotted your reply. >> > >> > It was easy enough to transpose and add 1 for the zero base. >> > >> > However the ADO function returns me once more to the start position of >> > mislaigned UsedRanges. On the bloated wbk it returned the last row as >> > 50918 >> > and not the real 98. >> > >> > I have been here before. >> > >> > MichDenis in another post some way back now supplied a link >> > http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid >> > the >> > pitfalls of flawed UsedRanges but is slow. >> > >> > This is frustrating because the incidence of flawed UsedRanges is only >> > about >> > 2 wbks in 500. But because of the risk, I have to use the slow method >> > on >> > every wbook. It would be great if I could detect a flawed UsedRange >> > and >> > run >> > the 2 recordset method on that wbk only. On the rest of the wbks I >> > could >> > use >> > SELECT COUNT(*) etc. >> > >> > FWIW I don't believe SELECT COUNT(*) does any counting at all because >> > it >> > is >> > so blisteringly quick. I think instead it probably uses the UsedRange >> > last >> > row or something like it. Unfortunately a null is a record to SQL so >> > if >> > the >> > wbk has been saved with a flawed UsedRange that is what it uses. >> > >> > So I am right back to square 1. If only I could detect a flawed >> > UsedRange >> > in a closed wbk……… >> > >> > Geoff >> > >> > >> > "RB Smissaert" wrote: >> > >> >> That code wasn't tested and indeed it is no good at all, mainly >> >> because I >> >> didn't consider the fact >> >> that an array produced by rs.GetArray is transposed. >> >> Shortly after I posted better code (via a phone), but it didn't come >> >> through. >> >> Try this code instead: >> > >> >>
From: Geoff K on 4 Oct 2009 15:24 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 15:29
I agree, SELECT COUNT(*), RecordCount, GetRows all work as expected when the UsedRange matches the real data. But all fail with a flawed UsedRange. Excel4Macros failed to run on the largest of the 2 wbks but not on the smallest. Unfortunately I have no quality control over these received wbks. Sometimes the quality is appalling, hidden rows, hidden columns, end of line characters, merged cells, cell errors, autofilters, some even without field names and of course some with flawed UsedRanges. I would be willing to supply the 2 wbks with known flaws were it not for data protection. They contain names, job titles, telephone numbers etc and it would be wrong of me to share those details. And of course if I deleted or overwrote the data the ensuing save would reset the UsedRange. But if you know of a way to create a UsedRange which is out of line then I am certain you would make the same observations. Geoff "RB Smissaert" wrote: > It works fine with me. > Could you mail me that workbook that gives you the wrong answer? > > RBS > > > "Geoff K" <GeoffK(a)discussions.microsoft.com> wrote in message > news:6F174DB2-DB9D-44EF-BB8E-02B32CA13423(a)microsoft.com... > > Hi > > > > I was just about to post the same thing when I spotted your reply. > > > > It was easy enough to transpose and add 1 for the zero base. > > > > However the ADO function returns me once more to the start position of > > mislaigned UsedRanges. On the bloated wbk it returned the last row as > > 50918 > > and not the real 98. > > > > I have been here before. > > > > MichDenis in another post some way back now supplied a link > > http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid > > the > > pitfalls of flawed UsedRanges but is slow. > > > > This is frustrating because the incidence of flawed UsedRanges is only > > about > > 2 wbks in 500. But because of the risk, I have to use the slow method on > > every wbook. It would be great if I could detect a flawed UsedRange and > > run > > the 2 recordset method on that wbk only. On the rest of the wbks I could > > use > > SELECT COUNT(*) etc. > > > > FWIW I don't believe SELECT COUNT(*) does any counting at all because it > > is > > so blisteringly quick. I think instead it probably uses the UsedRange > > last > > row or something like it. Unfortunately a null is a record to SQL so if > > the > > wbk has been saved with a flawed UsedRange that is what it uses. > > > > So I am right back to square 1. If only I could detect a flawed UsedRange > > in a closed wbk……… > > > > Geoff > > > > > > "RB Smissaert" wrote: > > > >> That code wasn't tested and indeed it is no good at all, mainly because I > >> didn't consider the fact > >> that an array produced by rs.GetArray is transposed. > >> Shortly after I posted better code (via a phone), but it didn't come > >> through. > >> Try this code instead: > > > > |