Export sorted recordset to Excel

[GeorgeDurkee]

I have a recordset with about 6000 records in it. Each record has 47 columns. I want to export it to Excel sorted on 7 fields.

Using the record by record export allows me to sort the data , then export it, but that takes about 15 minutes .

Using the Excel range.copyfromrecordset command takes about 5 seconds to export the data , but it uses the raw data and ignores the sort order .

Does anybody know of a way to export to Excel that is fast and allows me to sort these 7 fields ?

 

[KenReay]

Hi

Have you tried DoCmd.OutputTo command ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GeorgeDurkee]

Thanks for the feedback, but OutputTo does not appear to support Recordset procressing. It can output the Access intrinsics (table, form, module, query, etc.) but not recordsets. If there is a way to use it, please let post an example. I'm not seeing it.

 

[KenReay]

Hi

If it is a recordset, it came from a table or a query ? so just use that

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GeorgeDurkee]

It is a new recordset created in memory, using the fields.append method. It is based on the combination of about 8 tables, so using a standard query takes about 30 minutes just to select the data. Add the 30 to 45 minutes to do the record by record export so I can get the data sorted the way I want results in over an hour of processing time.

I tried writing the data to a temporary table and exporting it, but that took even longer.

By creating the recordset in memory, I can cut the time for selecting the data down to about 15 minutes. If I can use the copyfromrecordset command and get it sorted the way I need, that would mean the processing will go from over an hour to about 15 minutes. That would be great.

 

[KenReay]

Hi

I see rather scuppers my OutputTo idea....

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ggriffit]

check out

http://www.greggriffiths.org/webdev/both/excel/

apart from the tutorial one of the links looks at the performance of these methods.