I have been in a similar situation - although it was only 3 workbooks each
with 8 worksheets which had to be combined into one file to be linked.

So once I examined the data that was to come over, I created an alias field
for each needed column in the Excel spreadsheet. I then wrote a macro in
Excel to walk through each workbook, determine how many records in each and
from which column I was pulling the data for that field, place in each
record in the combined sheet, go to the next workbook, etc.

It works. Code is available if you wish to see it.

Don