expert advice needed on importing

[atol]

I am heaving hard time finding some info on the following. I am receiving daily about 10 spreadsheets with different formats (different header names); only 10-20 percent of the content is the same across all the spreadsheets. The output is that i need to convert all of these formats into a standard Excel template.
I am considering a few approaches. 1. Create a separate Excel macro for every sheet to convert into the required template. 2. Import every excel file in its own table in Access and then use quires to convert into one consistent format as the output requires, or 3. Normalize with macros the original spreadsheets, and then upload in only one table (which later will also have to be re-formatted to get to the right output.
I am leaning more to option 2, however wouldn't that be too much for Access? What if I have 50 different formats i might be getting - what is the lint of tables Access can handle?
I know it is a bit strange situation, but i was wondering if someone can help with some ideas, or share some experience, or best practice approach.

As always thank you all for your help.

Atol

 

[DavidAtWork]

Do all the spreadsheets have the same number of columns and do they always come in the same order even though as you state above, the column headers may be different
David

 

[atol]

Hi David,
No they don't. All spreadsheets are with different size, format, etc. My plan was to prepare a separate tepm table for every spreadsheet, then run a query to narmalize (or extract only the needed columns), and then store only that data. Not sure if it make sense.