Getting Excel Data Into Access

Question

Hi

I have to get the data in an Excel sheet into a Access database. The datastructures of the Excel sheet and the Access database are very different, so a lot of reformatting/restructuring has to be done. So I like to use VBA to import the data. I know that I can open the sheet in an Excel instance from VBA, then reading, converting and saving it in the tables. Is this the best way to do this, or is there a way to somehow load the entire sheet into Access/VBA and navigate on the data without an Excel instance open. Thanks.

Marcel

Answer 1

Why don't you import the Excel data into a temporary table (that matches the Excel spreadsheet) then copy it across to the proper Access table.

If it's a 1-1 record copy (but with renaming/transformations) you could probably do it using a query. Otherwise you can iterate through the inported Excel table in VBA.