What you seek can be done but you need VBA to do it best. Since you deny being a programmer, this is going to be a problem for you. I think you might be pleasantly surprised at how well it works that way. Here is an overview of the VBA approach.
1. Put all of the Excel workbooks in one folder. Doesn't matter much which one. Only matters that you know where it is and have access permissions that let you open the workbooks. It would be most CONVENIENT (but not NECESSARY) that the folder be one that you created for this purpose with nothing else in it.
2. Using the MSO File System Object you can scan the folder for all files of a given file type - like .xls - and get the list as a "collection" of FoundFiles or something like that. Access Help: File System Object - or FileSearch object will also do the trick. Also open a recordset to the table you wanted to use as the holder for this pile of data. Access Help: Recordsets and the OpenRecordset method of the database object.
3. Your VBA can step through the collection of files one at a time. (Top of outer loop). Access Help: Collections - and their properties and methods.
4. Your code can open an Excel Object on the current file. Access Help: Application Objects >> Excel Objects. There is also Excel Help on using VBA to operate in a workbook.
5. When you open the workbook, you can choose which sheet becomes the ACTIVESHEET - which is a "shortcut" to a particular sheet that was previously selected for attention. Look at the Excel Help on the Worksheets Collection, which is at the "top" part of the workbook collections hierarchy.
6. Once the activesheet has been chosen, you can traverse the collections in it. There are two major ones... Rows() and Columns(). If your data sets are in rows (most likely) then you traverse each row in the collection. Excel Help: Rows() collection. This step will also be the top of an inner loop.
7. Within a row you can access the columns of that row as part of each row's Cells collection. Excel Help: Cells() collection and the contents of a Cell object, which isn't atomic. It has lots of structure and the .Text component isn't necessarily on top of that structure. As I recall, there is something between a cell and the text, but it is a constant structure and easy to address or bypass. Something like ...
ActiveSheet.Rows(x).Cells
.Range.Text - or approximately like that.
8. When you have it this far, you are ready to do your thing. When you select a new spreadsheet row, create a new record in the recordset with the .AddNew method (see Access Help on recordsets and their methods.)
9. Randomly address the cells in the worksheet to pick out the data you want. Pick out the text in each cell. Put it in one field of your recordset. This MIGHT be a loop but might just be in-line code if there is not that much to pick out. If you have type conversions to do because the spreadsheet returns text but you really wanted a date, this is where you do it.
10. When the fields are all safely tucked away, do .Update on your recordset (see Access Help on the Recordset methods) and step to the next row of the Rows collection. (End of inner "rows" loop.) You are done with this loop when you reach a row number equal to Rows.Count - i.e. the last row of the activesheet you selected.
11. Close the spreadsheet. Go back to the top of the outer loop to get a new spreadsheet (next element of FoundFiles - or is it FilesFound...? I always get those confused, which is why I always advise you to read the help files).
12. When you are out of files, close the recordset. Your import is done.
A couple of rules of thumb to consider. If you opened it, close it. If you created it, destroy it. If you set a variable to it, reset the variable.
I.e. Set RS = CurrentDB.Openrecordset ... needs to pair with RS.Close
Set XLO = {an excel application object} ... needs to pair with XLO.quit and followed by Set XLO = nothing
XLO.{open a workbook} ... needs to pair with XLO.Close
Sort of like your Mom always told you when you were a kit. If you open it, close it. If you turn it on, turn it off. If you take it out, put it away. See, not that hard. Sort of like kindergarten stuff.
Unlike your Mom, who probably just gently chided you when you forget, Access and Windows are more stringent. Like... if you screw up you will start getting "Out of Memory" (or out of something else) notices. And things will stop working.
