Pasting records into Excel with QueryTables
 

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

Pasting records into Excel with QueryTables
 

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

Pasting records into Excel with QueryTables
 

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



"John Green" wrote in message ...
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" wrote in message
om...
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

Pasting records into Excel with QueryTables