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

I'd like to have this too. My team makes imports every day from various sources -- different formats but mostly standard data. The data is not stable enough to support an Access Import Specification, so I want human-guided fuzzy mapping and declaration. It should look like the common wizard for migrating contact information to a new app, where 'Source.ZIP' is already matched with 'Target.ZIP' and 'NameLast' is ready to be matched with 'Last Name'. — Smandoli
– Smandoli, Commented Jun 3, 2014 at 13:52

Fionnuala · Accepted Answer · 2011-02-22 11:38:23Z

3

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.

answered Feb 22, 2011 at 11:38

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David-W-Fenton Over a year ago

TransferSpreadsheet/TransferDatabase/TransferText date back at least to Access 2.

David-W-Fenton Over a year ago

Also, to add to @Remou's advice, you can use the wizard to create an import spec that you can then use with TransferSpreadsheet to insure that data type mappings are consistent.

jim31415 · Accepted Answer · 2018-10-05 13:40:15Z

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

edited Oct 5, 2018 at 13:40

jim31415

8,8186 gold badges45 silver badges68 bronze badges

answered Feb 22, 2011 at 8:02

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

3 Comments

chris neilsen Over a year ago

Can you be a little more specific: version of Office, import as linked/appended/new table, how you want to trigger the import, vba code at Excel or Access end?

Jobin Over a year ago

I am using office 2007. I want to use this to a form in access

chris neilsen Over a year ago

Sorry, I don't see the need for VBA yet. If you use External date/Import/Excel you have the excel data in Access as a linked table, you can then create a form that refers to this table as you see fit (refresh on demand, filter, query, whatever). If you have other requirements that might push us toward vba please share them, so we can offer more focused advise

Nathaniel_613 · Accepted Answer · 2011-02-24 04:59:01Z

0

if this task is something that u need to perform on a regular basis, you may want to consider creating a macro and scheduled task to perform this automatically. it is very easy to settup and I can outline procedure if need be

answered Feb 24, 2011 at 4:59

Nathaniel_613

1794 gold badges15 silver badges40 bronze badges

Collectives™ on Stack Overflow

Import Excel Data programmatically to access table with column mapping

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related