Pasting records into Excel with QueryTables

[Saul Margolis]

Hi,

I need to retrieve a recordset and paste into Excel. Initially, I
used a cursor and iterated through the recordset, pasting each field
into a cell. I found this really slow. I then read about
querytables, and have used this instead. It is so much faster, its
unbelievable.

The problem I have is with the number of records. Sometimes the
recordset can contain 70000 or 80000 records, and a single worksheet
only accepts 65536 rows. I would like to be able to get Excel to move
onto the next sheet once it has exhausted the number of rows. Is this
possible?

I have tried using the PageSize and AbsolutePage properties, but the
Querytables function seems to ignore these and tries to use the entire
recordset anyway.

I am currently trying GetRows and AddNew to write all txns to array,
and then write 65536 txns back to a recordset that I can use with
QueryTables. I'm not sure how to get a blank recordset that I can use
AddNew with?

I'm really not sure if I'm going about this right, does anybody have
any ideas?

Thanks in advance,
Saul Margolis

 

[John Green]

Saul,

Presumably you were using ADO initially. You are probably best off sticking
with ADO (or DAO). Pasting fields individually is very slow. You should
check out the CopyFromRecordset method of the Range object that allows you
to dump your data very efficiently into a range.

You would still need to control the number of rows in a record set but you
should be able to do this in your SQL.

John Green
Sydney
Australia

 

[Saul Margolis]

Hi John,

Thanks. I haven't seemed to have found a way to split up a recordset
within Excel, so have implemented server-side paging.

Regards,
Saul

 

[Jamie Collins]

(e-mail address removed) (Saul Margolis) wrote ...

Thanks. I haven't seemed to have found a way to split up a recordset
within Excel, so have implemented server-side paging.

If you are using ADO, something else to consider is using Filter with
GetRows. Filter is a way of applying an additional a WHERE clause to
filter the recordset. GetRows returns the recordset to an array which
can be used to populate an Excel range. If a Filter has been applied
to the recordset it will be applied when using GetRows.

The trick is finding a way of filtering to ensure you have less than
65536 rows. If you don't have the consistent data with known key
column(s), this could be an issue. It would be good if GetRows (or
Filter) could be linked to the PageSize property :-(

Jamie.

--