Best way to read an Excel file into an Access database

excel ms-access ms-access-2007 vba office-2007
waanders picture waanders · May 25, 2010 · Viewed 29.4k times · Source

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

HansUp picture HansUp · May 25, 2010

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.

Related questions

Record cannot be read; no read permission on 'MSysObjects'

I'm trying to get a list of all tables from an Access 2007 ACCDB format database using Excel VBA. I have followed this post: How can I get table names from an MS Access Database? Using: SELECT MSysObjects.Name AS table_…

Read More
Get length of array?

I'm trying to get the length of an array, yet I keep getting this error: Object required Am I doing something wrong? Dim columns As Variant columns = Array( _ "A", "ID", _ "D", "Name") Debug.Print columns.Length ' Error: Object required

Read More
Error: "Could Not Find Installable ISAM"

I've written some VBA code in an Excel workbook to retrieve data from an Access database in the same directory on a desktop. It works fine on my machine and several other machines running Windows XP, but when we tested …

Read More