Import Excel Data programmatically to access table with column mapping

Question

I am in search of a tool / VBA code snippet that can port my excel data to my access table with effective columns mapping. Anybody please share if you have any idea on these.

Thanks in advance

Answer 1

The standard way to import or link Excel in Access is to use the TransferSpreadsheet method of the DoCmd object. The link is for 2007, but it has been available certainly since 2000, if not before. TransferSpreadsheet includes a HasFieldNames argument.

Answer 2

In Access 2007 you can use External Data/Import/Excel

Just follow the wizard to select the workbook, type of connection, sheet and data. Data can be imported to a new table, appended to an existing table, or as a linked table

In Access 2003 its File/Get External Data/Import or File/Get External Data/Link tables select file type .xls and browse to the workbook