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