Need a quicker solution

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone suggest a faster way to copy a recordset to Excel via automation.
I'm currently using the following code, which copies each field across one at
a time. The recordset has 7 columns and, at times, 10000 records, so it's
taking forever to populate a worksheet. Here's the section of code that
handles the data transfer:

Set rstTasks = db.OpenRecordset(strTasksSQL, dbOpenSnapshot)
If rstTasks.EOF = False And rstTasks.BOF = False Then
rstTasks.MoveFirst
Do While rstTasks.EOF = False
For lngColumn = 0 To rstTasks.Fields.Count - 1
XLCell.Offset(0, lngColumn).Value = rstTasks.Fields(lngColumn).Value
Next lngColumn
rstTasks.MoveNext
Set XLCell = XLCell.Offset(1, 0)
Loop
End If

I was hoping there would be a command to copy the entire recordset in one
hit and just paste that into the worksheet. The workbook has x amount of
worksheets, so Access is creating them on the fly, formatting them and naming
them appropriately, so the OutputTo command is not an option here.
Thanks in advance
Dave
 
Hi,

I am not sure this would help, but just a thought , would it be possible to
dump them into a text file i.e. comma sperate file and populate excel sheet
using thet text file?

I am really not sure if this would be fast ot this would be more code.

Anand
 
Dave said:
Can anyone suggest a faster way to copy a recordset to Excel via
automation.
I'm currently using the following code, which copies each field across one
at
a time. The recordset has 7 columns and, at times, 10000 records, so it's
taking forever to populate a worksheet. Here's the section of code that
handles the data transfer:

Set rstTasks = db.OpenRecordset(strTasksSQL, dbOpenSnapshot)
If rstTasks.EOF = False And rstTasks.BOF = False Then
rstTasks.MoveFirst
Do While rstTasks.EOF = False
For lngColumn = 0 To rstTasks.Fields.Count - 1
XLCell.Offset(0, lngColumn).Value =
rstTasks.Fields(lngColumn).Value
Next lngColumn
rstTasks.MoveNext
Set XLCell = XLCell.Offset(1, 0)
Loop
End If

I was hoping there would be a command to copy the entire recordset in one
hit and just paste that into the worksheet. The workbook has x amount of
worksheets, so Access is creating them on the fly, formatting them and
naming
them appropriately, so the OutputTo command is not an option here.
Thanks in advance
Dave
Click to expand...



This should be faster.
I guess you don't need an explanation of how to call it or how it works, but
let us know if it improved things.


Private Function ImportData(xlSheet As Excel.Worksheet, _
strSQL As String, _
strRange As String) As Boolean

On Error GoTo Err_Handler

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly)

With xlSheet
.UsedRange.ClearContents
.Range(strRange).CopyFromRecordset rst
End With

ImportData = True

Exit_Handler:

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & CStr(Err.Number)
Resume Exit_Handler

End Function
 
Brian,
That was brilliant. Not only did I knock out a few lines of code, but the
transfer completed in one third of the time.
For anyone who's interested, I found two differences in the two methods.
The CopyFromRecordset method resulted in a slightly larger spreadsheet file
and all cells are left aligned - even numbers...
Thanks Brian.
Dave
 
Dave said:
Brian,
That was brilliant. Not only did I knock out a few lines of code, but the
transfer completed in one third of the time.
For anyone who's interested, I found two differences in the two methods.
The CopyFromRecordset method resulted in a slightly larger spreadsheet
file
and all cells are left aligned - even numbers...
Thanks Brian.
Dave
Click to expand...


Thank you for letting us know.
It is this kind of feedback that keeps us posters posting!


 
Dave said:
Brian,
That was brilliant. Not only did I knock out a few lines of code, but the
transfer completed in one third of the time.
For anyone who's interested, I found two differences in the two methods.
The CopyFromRecordset method resulted in a slightly larger spreadsheet
file
and all cells are left aligned - even numbers...
Thanks Brian.
Dave
Click to expand...


Thank you for letting us know.
It is this kind of feedback that keeps us posters posting!



 
Back
Top