[AccessD] Feeding Data into Access from Hundreds of Excel Files with no Common Definition

All,  

I work for a 50 person firm that is in the employee recognition
business.  For the past 15 years, our customers (companies) have sent
preliminary order information to us primarily via Excel files.  The
catch is that there is currently not a "common definition" for these
Excel files.  Therefore, incorporating this data into our Order Entry
System (Access 2007 based) requires a lot of manual "cutting and
pasting".  In the past, I have proposed that we institute a common
definition, but our Marketing/Sales people are not in favor of this as
it would shift the burden to our customers.

A couple years ago, I tried to automate the processing of these Excel
input files with Access VBA code.  I gave up on this approach as things
became too complex due to the hundreds of slightly different Excel file
definitions.

Recently, I have started to work on a new "semi-automated" approach.
This approach would utilize a "Staging" Excel file which will have a
single common definition.  Once the data is in this Staging file, it
will be easy to move the data into the Access database.

To reduce the amount of manual work of getting the data from the
original Excel files (various definitions) into the Staging Excel file
(one definition), I have started to build a number of Excel Macros.
These Macros can be used for such things as (1) set names to Proper Case
(2) Move the location of columns (3) concatenate two columns to create a
new column (4) eliminate quotes, hyphens, etc. (5) rounding (6) and
several other types of data manipulation.

With all of this background info, I have a couple questions.

Has anyone else ever had to deal with a similar situation of hundreds of
Excel files with slightly different definitions?

If so, what approach was used?

Thanks,
Brad