excelms-accessms-access-2007vbaoffice-2007

Best way to read an Excel file into an Access database

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.

Solution

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