Transfer data from a recordset to a worksheet (ADO)

If you don't want to use the CopyFromRecordset method (Excel 2000 or later) you can use the procedure below to transfer data from a recordset to a worksheet. Call the procedure like this:

RS2WS rs, Range("A3") ' rs is an ADO recordset variable

Sub RS2WS(rs As ADODB.Recordset, TargetCell As Range)
Dim f As Integer, r As Long, c As Long
    If rs Is Nothing Then Exit Sub
    If rs.State <> adStateOpen Then Exit Sub
    If TargetCell Is Nothing Then Exit Sub
    
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .StatusBar = "Writing data from recordset..."
    End With
    
    With TargetCell.Cells(1, 1)
        r = .Row
        c = .Column
    End With
    
    With TargetCell.Parent
        .Range(.Cells(r, c), .Cells(.Rows.Count, c + rs.Fields.Count - 1)).Clear 
        ' clear existing contents
        ' write column headers
        For f = 0 To rs.Fields.Count - 1
            On Error Resume Next
            .Cells(r, c + f).Formula = rs.Fields(f).Name
            On Error GoTo 0
        Next f
        ' write records
        On Error Resume Next
        rs.MoveFirst
        On Error GoTo 0
        Do While Not rs.EOF
            r = r + 1
            For f = 0 To rs.Fields.Count - 1
                On Error Resume Next
                .Cells(r, c + f).Formula = rs.Fields(f).Value
                On Error GoTo 0
            Next f
            rs.MoveNext
        Loop
        .Rows(TargetCell.Cells(1, 1).Row).Font.Bold = True
        .Columns("A:IV").AutoFit
    End With
    
    With Application
        .StatusBar = False
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

The macro example assumes that your VBA project has added a reference to the ADO object library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft ActiveX Data Objects x.x Object Library.

Document last updated 2001-11-27 12:46:39 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-11-11 17:57:17 CET):

Re: Write in a closed workbook
If you are going to write data to a workbook, it will have to be "opened" in one way or another.
If you want to prevent the workbook from being visible to the user, do something like this:

' do your data query and populate a recordset, this is not shown here

Application.Screenupdating = False ' turn off screen updating
Workbooks.Open("C:\Folder\MyData.xls") ' open a workbook
' write the content of the recordset to the workbook
RS2WS rs, Range("A3") ' rs is an ADO recordset variable
Workbooks(MyData.xls").Close True ' save and close the workbook
Application.Screenupdating = True ' turn on screen updating

The user will now not be able to see that a workbook is beeing opened, updated, saved and closed.

Caroline C from Québec, Canada wrote (2006-11-10 22:27:54 CET):

Write in a closed workbook
Is it possible to modify the sub RS2WS so my datas could go in a closed workbook?

Ole P. from NORWAY wrote (2006-02-05 00:16:13 CET):

Re: Import data from Excel to Excel (ADO)
You can find an example showing how to do this here.

truhi wrote (2006-02-04 16:55:51 CET):

Call this procedure
Thanks!
Re: Call this procedure

I See this page for a complete example.
but, this example: "Import data from Access to Excel (ADO)"

I need "Import data from Excel to Excel (ADO)"

Please a example about (RS2WS).

Good luck!

Peter Keery from London, UK wrote (2006-01-19 12:40:30 CET):

Thanks!
Very useful thank you! Saved a lot of time and effort.

Cheers

Pete

Ole P. from Norway wrote (2006-01-02 18:34:45 CET):

Re: Call this procedure
See this page for a complete example.

truhi wrote (2006-01-02 18:27:14 CET):

Call this procedure
Sorry, I am beginner.

I am unable to call RS2WS, can't use parameter (rs, TargetCell)
How can I call this procedure (RS2WS) ? please a example.
truhioglu@yahoo.com

Many thanks....

Ole P. from Norway wrote (2005-03-17 22:01:01 CET):

Re: Definition of .Formula
This line:
.Cells(r, c + f).Formula = rs.Fields(f).Value
will put the value of a field in the recordset into a worksheet cell.

Kris wrote (2005-03-17 17:31:01 CET):

Definition of .Formula
What does the ".Formula" do in the following statement:
Cells(r, c + f).Formula

Jason from Chicago, IL wrote (2005-03-17 05:41:04 CET):

Re: Transfering a selection of the recordset
Please disregard my previous question. I figured it out that I would just need to increment f. This is a very helpful site. Thanks!