Hi Mike,
If all the worksheets are exactly the same formatting wise and starting in the same place then that will make it easier.

There is an option when you are importing data in access to save the import steps so you could re use them but this will prob still need you to chose one file at a time.

VBA
There is a method called TransferSpreadsheet that you can use.

MSDN Article

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

Example
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table3", "C:\Users\\Documents\Book1.xlsx", False, "A1:G12"

Now just get a list of all the files and loop through them all replacing FileName with the actual file name and they will all get imported into your newly created table.

Test one first and see if it is pulling all the records through.

For the Range you may want to select all rows in the used columns just in case.
This may cause import errors for blanks but still pulls all records through.
You might want to turn warnings off but that is a whole other kettle of fish.

Hope this helps you get started.
Alex