What you want is possible in one step, but would require a lot of code to find the file, open Excel, go to each tab, grab the data and run a make table query or process ADO or DAO based code to make the table. There would be no linking required.
I suggest you link, since you have the code, then run make table queries on each one, then delete the linked table. How complex this would be might also depend on whether or not your final tables must have the exact same name as the linked ones. You cannot make a table with the same name as the existing linked, so a temporary name or variation of it would be necessary. Temporary names would complicate the process - you would have to rename the temp back to the name of the linked once the linked was deleted.
Perhaps you will decide it's not worth the effort. I presume you have a very good reason for making the table local instead of just leaving it linked? You realize that if the source data changes, the local table is static and will not reflect any changes made in the source file?
