Clone existing recordset to excel recordset

Question

I have a recordset that contains all of the data needed to import into an Excel file. Is there a way I can clone my live recordset into the one created when I open an Excel file?

Here is what I'm trying right now with no luck.

Dim connection : Set connection = Server.CreateObject("ADODB.Connection")

connection.Open "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; 
                 Dbq=c:\MyExcel.xls;" & _
             "DefaultDir=c:\; ReadOnly=False;"
Dim excelRecordset : Set excelRecordset = Server.CreateObject("ADODB.Recordset")
excelRecordset.Open "[SHEET1$]", connection, 2, 3

excelRecordset.AddNew
Set excelRecordset = recordset.clone

excelRecordset.Update
excelRecordset.Close

Thank you.

Answer 1

When you do this:-

Set excelRecordset = recordset.clone

You replace the reference to the recordset opened on the excel spreadsheet (and it closes) with a reference to new recordset. This new recordset is not in anyway connected to the excel spreadsheet.

Using recordsets you don't really have much choice but to For Each through the source recordset, AddNew on the destination, assign each field from source to destination and Update.

Answer 2

Excel Range objects have a CopyFromRecordset method that should do the job. If you can create an Excel.Application object and open the workbook you want to write to then you should be able to use this.

So if your existing recordset is named rs and has already been populated, the code in VBA would be:

Worksheets("SHEET1").Cells(2, 1).CopyFromRecordset rs

You can then iterate over rs.Fields to fill in the field names

Dim fld As Field
Dim i As Integer
i = 1
With Worksheets("SHEET1")
    For Each fld in rs.Fields
        .Cells(1, i).Value = fld.Name
        i = i + 1
    Next fld
End With

Not sure how easy that would be to translate to your scenario though.

Alternatively, if it is available with the driver you are using, you may want to look into the SELECT INTO and INSERT INTO syntax described in this Knowledge Base article