VBA to bring Excel (range) TABLE back into Access

[CedarTree]

Hi - I have some good coding that takes a recordset in Access and plugs it into a range in Excel (so that I don't have to move cell by cell). But does anyone have a trick to take a whole range in Excel and suck it back into an Access table, without going cell by cell?
Thanks!

 

[Minty]

TransferSpreadsheet. https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet

It works both ways.

 

[CedarTree]

So to transfer from recordset to excel, you can just use this:
ExcelRange.CopyFromRecordset RecordsetObjectName

Nothing similar to go back to Access? For Transfer Spreadsheet, I wonder if you have to save the Excel file each time (say you're running back calcs)?

I'd like to import from Excel while the Excel file / object is open.

 

[CedarTree]

I think I can use this? Anyone have hints / things to watch out for?
https://support.microsoft.com/en-us...orkbooks-bfb26f12-ba6a-91be-7fd4-4aadf1ff1afa

 

[CedarTree]

So I have two recordsets open using ADO. One holds the Excel table. One is referring to an Access table. is there a quick way to say Contents of RS1 get added to Contents of RS2?

 

[Minty]

TransferSpreadhsheet will do what you want without a recordset.
Or - link to the excel sheet and simply run a query against it to insert records to your table.

Numerous examples here https://access-excel.tips/import-workbook-access-transferspreadsheettransferspreadsheet/

 

[CedarTree]

It doesn't seem to work if you have a worksheet open as an object.