Copy DAO recordset from Excel into Access table

I have searched endlessly for a solution to this but can’t figure it out at all. Any help would be massively appreciated.

I currently have some Access VBA code which sets up a DAO recordset connection with a range in an excel worksheet. (I have avoided linking the spreadsheet as a linked table as I am unsure if you can do this with just a specific range and unfortunately, editing the spreadsheets in any way is not an option).

I then need to copy this data into a table. I am currently looping through each record and adding it to a new recordset as shown below:

Set rsTbl = CurrentDb.OpenRecordset("tblData")
dbpath = "S:OPSAgent Performance.xls"
Set db = OpenDatabase(dbpath, False, True, "Excel 8.0;HDR=Yes;")
Set rst = db.OpenRecordset("SELECT * FROM [AgentActivity$C7:AP1000]")

Do Until rst.EOF
    rsTbl.AddNew
    For Each fld In rst.Fields
        rsTbl(fld.Name) = fld.Value
    Next fld
    rsTbl.Update
rst.MoveNext
Loop

This works well enough but I need to loop through a lot of records in each workbook and also loop through a lot of workbooks. I was hoping there was a way of dumping the entire recordset into tblData instead of having to loop through as I am currently.

Does anyone know a better way?

Answer

Well after all this time of searching for a solution, I finally reach the point where I ask for help and then I figure it out anyway.

I’ve used the transferSpreadsheet method to just link the range I need and then an INSERT INTO statement to move the data to tblData

DoCmd.DeleteObject actable, "tblTemp"
path = "S:OPSAgent Performance.xls"
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "tblTemp", path, True, "AgentActivity!C7:AP1000"
CurrentDb.Execute "INSERT INTO tblData SELECT * FROM tblTemp"

I can then loop to the next workbook and overwrite the tblTemp. It’s running much more quickly now. There may be another way which is better still but at least I’ve made progress. Hope this is useful for someone else.