slow exporting array from access to excel

0
votes

I have a similar performance issue to that in slow exporting from access to excel, exporting data to MS Excel by setting individual cell values looping on:

objSheet.Cells(table_1_row_no + intcounter, table_1_colA_col_no) = table_1_array(intcounter, 0)

however my data comes from an array, and needs to be processed in excel after transfering it there, hence i need to have an open sheet to work with after transfering the data. As a result non of the methods noted in the above post appear ideally suited.

I have an MS access application that pulls data from the database does a fair bit of processing of the data in vba prior to it being put into excel. As a result the data to be transfered is in array rather than a recordset. The simplest way to get it back to a recordset to allow the .CopyFromRecordset method to be used, is to create a temporary table and recordset, but this seams a fairly lengthy path to go down unless theres nothing better.

The spreadsheet does a lengthy calculation before Access picks a result back. I'm keen to leave this in excel as this gives flaxibility for engineer's using it to make adjustments where needed for differant situations (the environment is controlled so unauthorised changes arent a concern). As a result options that simply drop out a csv, or an excel file without opening it aren't suited either.

Any suggestions much appreciated.

1
excelvbams-accessexport-to-excel
Why have you got an array? Why not build the data into a recordset in the first place? – Fionnuala
Create a new 2-D array containing the values you want to transfer then copy that array to the sheet in a single operation. objSheet.Cells(table_1_row_no, table_1_colA_col_no).resize(numRows,1).Value = yourArrayHere – Tim Williams
2D array has provided a quick way to get a significant performance imporvement - Thanks Tim. – Nathan
For some arrays the data can come from a recordset easily. For others theres alot of processing in vba to calculate the relivant figures after extraction from the database, while for others the data consists of a figure from one source and a figure from another etc etc. As a result several situations don't lend themselves to creating a recordset based on a table or query. – Nathan

1 Answers

0
votes

I had the same problem, try to convert your data source to recordset If you use EXCEL 2000,2002,2003, or 2007 then Use CopyFromRecordset

'Copy the recordset to excel sheet we  start in cell A1
 sheet1.Cells(1, 1).CopyFromRecordset rst

Be carefull CopyFromRecordset will fail if recordset contains OLE object field or array data such as hierarchical recordset