I am very new to Access and this is for a school project. So if this is not proper technique let me know. Right now the words are separated by capital letters. So a capital letter is the beginning of each word.
That is good naming technique for objects. But, what you are asking to do is to parse that object name. If the parsing has to happen on finding the capital letters, then the parsing must be done in a code loop character by character and when you find a capital letter, that triggers the start of a new field. With that concept in mind, what rule would you apply to determine which parts of the word contain the data that you want.
Given what I said, take a look at the second table name - GFSBakedGoodsImport. That ends up in an array as
G, F, S, Baked, Goods, Import
I'm guessing that won't work because GFS is probably a company name abbreviation given the other table names that I can see.
I think you should probably step back and look at the problem as a relational database rather than as a series of spreadsheets.
Do all the spreadsheets have the same format? How are the spreadsheets named? Perhaps rather than importing the spreadsheets, you should link to the active one and use a common name so that when you link to a spreadsheet, the Access name becomes Link_Import or something generic. If the spreadsheet names have separators such as "_", that would make breaking them into parts much easier. So GFS_BakedGoods.xlsx can be parsed into GFS for the company name and BakedGoods as the product. As you can see, using capital letters isn't going to work for this task.
I'm assuming this is an ongoing import so it will be important to automate it. Somehow a human is looking at the spreadsheet name and determining how to name the table. You just have to come up with rules that will allow this to be done with VBA.