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" wrote in message
...
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" wrote in message
...
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: