Dynamic field mapping with excel import

[PVSyemya]

Hi,

I'm new to access2007 but have a bit of experience with VBA in excel. I
inherited a lot of excel files that I'm trying to import into a single table
in Access. I've written a short VBA program to cycle through the excel files
and grab the relevant ranges from the various sheets in each workbook. Which
is how I discovered that most of the forms have small differences that cause
Access to throw an error during the import process. The excel files are
essentially forms but unfortunately there was no quality control on the forms
so that the header information varies slightly from file to file. For
instance there are variations in the abbreviations that were used. Column
order varies such that one form may have headers A-B-C and another might have
b-C-A. Some titles are split across 2 rows. The list goes on and on and here
are too many files to manually fix the headers in each file.

Is there a way to dynamically map excel columns to access fields as each
excel file is opened. Using the above example A=A B=b and C=C.

Thanks in advance,
PV

 

[PieterLinden via AccessMonster.com]

Hi,

I'm new to access2007 but have a bit of experience with VBA in excel. I
inherited a lot of excel files that I'm trying to import into a single table
in Access. I've written a short VBA program to cycle through the excel files
and grab the relevant ranges from the various sheets in each workbook. Which
is how I discovered that most of the forms have small differences that cause
Access to throw an error during the import process. The excel files are
essentially forms but unfortunately there was no quality control on the forms
so that the header information varies slightly from file to file. For
instance there are variations in the abbreviations that were used. Column
order varies such that one form may have headers A-B-C and another might have
b-C-A. Some titles are split across 2 rows. The list goes on and on and here
are too many files to manually fix the headers in each file.

Is there a way to dynamically map excel columns to access fields as each
excel file is opened. Using the above example A=A B=b and C=C.

Thanks in advance,
PV

One way to deal with something like this is to create a table of column
"aliases".
e.g.
CREATE TABLE ColumnAliases(AliasName TEXT(50) PRIMARY KEY, MatchingColumn
TEXT(50));

Then you could write some code to look up each column name in your Excel file
that would match to your columns in Access, and create your SQL
statements/queries for you.