Need help on slow export routine

[Mr. Smith]

Hi.
I have a recordset based on a query that I export to Excel trough this
"approach".
NB: The array arr_col() is 36 position array holding column letters,
starting with arr_col(1) = a, arr_col(2) = b etc. I totaly mess up trying to
use R-1C-1 etc.

'Writes column headings
For i = 0 To rs.Fields.count
objXL.Range(arr_col(i + 1) & "6") = rs.Fields(i).Name 'Starts with
column headings in row 6
Next i
row = 7 'Set first datarow
Do Until rs.EOF 'Write recordset to worksheet
For i = 0 To rs.Fields.count
objXL.Range(arr_col(i + 1) & row) = rs(i)
Next i
'objXL.Range("A4").Select 'Show import status directly in Excel
sheeet
'objXL.Selection = "Imports: " & row - 6 & " of " & rscount
row = row + 1
rs.MoveNext
Loop

This is working slooooow (but it works)

Where can I "optimize" this export routine. Is there a "dump/bulk copy"
routine I colud use.....

Any hints appreciated

Kind regards
Mr. Smith.

 

[Klatuu]

There are a couple of ways you might do this.
One would be to use the TransferSpreadsheet method and use the Range
argument to identify placement of the data.
The other, since you are already using automation, would be to use the
CopyFromRecordset method.
To use it, you activate a cell in a worksheet, then use the
CopyFromRecordset and it will copy the data starting at that location.

 

[Mr. Smith]

Damn I should have dug deeper into Excel VBA.....
CopyFromRecordset was "TURBO-LOAD"

Thanks alot Dave