Copy large data from Access to Excel *SOLVED*

[lintz]

I have about 5000 records I need to copy into an existing excel spreadsheet from an Access table. I have tried the below but it is way to slow. Does any one know of a much quicker way I can paste the 5000 records into Excel?

Thanks.

Do Until rsSelectedTrades.EOF = True
objExcel.Cells(RowActive, 80).Value = rsSelectedTrades!ShareCode 'Column 80 is CB
objExcel.Cells(RowActive, 81).Value = rsSelectedTrades!StockTradeNo
objExcel.Cells(RowActive, 82).Value = rsSelectedTrades!EntryDate
objExcel.Cells(RowActive, 83).Value = rsSelectedTrades!Enter
objExcel.Cells(RowActive, 84).Value = rsSelectedTrades!Direction
objExcel.Cells(RowActive, 85).Value = rsSelectedTrades!Exit
objExcel.Cells(RowActive, 86).Value = rsSelectedTrades!HoldPeriod
objExcel.Cells(RowActive, 87).Value = rsSelectedTrades!Profit
objExcel.Cells(RowActive, 88).Value = rsSelectedTrades!ProfitPer
objExcel.Cells(RowActive, 89).Value = rsSelectedTrades!Other
objExcel.Cells(RowActive, 90).Value = rsSelectedTrades!Signal
objExcel.Cells(RowActive, 91).Value = rsSelectedTrades!ExitDate 'Column 91 is CM

rsSelectedTrades.MoveNext
objExcel.ActiveCell.Offset(1, 0).Select
RowActive = RowActive + 1
Loop

[RhinoBull]

You can do that using Access automation:

Private Sub ExportExcelSheet(strMdbPath As String, _
                             strExcelPath As String, _
                             strTable As String)
'======================================================
Dim AccessDB As Access.Application
 
    Screen.MousePointer = vbHourglass
    Set AccessDB = New Access.Application
    AccessDB.OpenCurrentDatabase strMdbPath
    AccessDB.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
                                       strTable, strExcelPath, True
    AccessDB.Quit acQuitSaveNone
    Set AccessDB = Nothing
    Screen.MousePointer = vbDefault
 
End Sub

[lintz]

Thanks RhinoBull, i'll give it a try.

[Bruce Fox]

I was up against something similar. CopyFromRecordset (Excel 2000+)
may be an option for you.

Here was my situation (see the last post) here.




Bruce.

[lintz]

Bruce, that link worked a treat. Thanks a lot