Best way to read an Excel file into an Access database

Question

What's the "best" way to read (just read) an Excel file from within an Access 2007 application. I only want to loop trough the rows and put the data into an Access table.

I don't want a manually import (Get External Data dialog) but by VBA. The user gets a Form with a Browse button and then points to a Excel file with a defined content/format. After that the VBA code reads the data and puts it into the Access database.

Answer 1

You could try the DoCmd.TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acImport, , "from_excel","C:\Access\demo.xls", True

That imports spreadsheet data into a table named from_excel, and assumes the first row of the spreadsheet contains field names. See Access help for TransferSpreadsheet or online here, for more details.