onedaywhen wrote:
Jake,
Interesting. I didn't know that the 5K limit (as mentioned in Q246335)
no longer applies. I have my own (now little used) CopyFromRecordset
variation which uses your suggested 'error handling' approach i.e. if
Application.Transpose causes an error then do it the long way. I just
tested it with a 65536x10 results set: no limitation was reached but I
gave up waiting for the 'long way' to finish!

Likewise, I didn't know (or more likely, I forgot) that the limitation was
no longer there in 2002 and up. But like I said, with a long varchar
column, I ran into another limit anyway. <g

One thing I've considered (but haven't got round to building a proper
example) is to use the recordset's 'pages' e.g. some quick code:

With rs
.ActiveConnection = Con
.CursorType = adOpenStatic
.Source = "SELECT MyCol1 FROM TakesMuchTime"
.Open
.PageSize = 65535
' Interate through Pages somehow!
' e.g. to do the last page:
.AbsolutePage = .PageCount
Sheet1.Range("A2").CopyFromRecordset rs
End With

Depends on the data but one way of iterating through the pages would
be to Sort/ORDER BY on a key column (or columns), iterate pages using
.AbsolutePage, get the key column values for the first rows of each
page, then on a second pass use the key values in the Filter method to
limit the rs to 65536 (or less) for CopyFromRecordset on each
iteration. Rather than two passes, it would be easier to Clone the
recordset and use the two recordsets side-by side when filtering, of
course.

I used the PageSize and PageCount properties to find the number of
worksheets needed, but I couldn't think of a good way to do the second part.
So that's why I used a GetRows, as it gave me what I was looking for
(allowed me to fetch the next <PageSize rows). Doing a CopyFromRecordset
would be ideal here, though. If you had control over the data, then you
could obviously use some type of key column as you mentioned, then use a
Filter that gives you the next <PageSize rows, Clone the Recordset, then
use CopyFromRecordset to dump the data to the worksheet. Hmmm, I'll have to
think about that a bit more to see how it could be extended for data sources
you *don't* have control over.

A little project for one day when I've more time on my hands,
methinks.

That's always the problem, isn't it? <sigh

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]