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.
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
.