what is the purpose of linking - why not just import the data?
And what do you do with the data once linked? Normally it would be a one time exercise to create the linked tables
Only way to do this 'in one go' would be to use vba code. You could look at using the transferspreadsheet function multiple times to create the linked tables - see this link https://docs.microsoft.com/en-us/off...ferspreadsheet. you would just need to change the range.
Another method is to use a query - but depends on what you are doing. This sql compares data in two worksheets and returns those that are in the first worksheet, but not the second
Code:
SELECT XL1.*
FROM [sheet1$A:D] AS XL1 LEFT JOIN [sheet2$A:D] AS XL2 ON XL1.firstname=XL2.firstname IN 'C:\pathtofilename\myxlfilename.xlsx'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes] WHERE XL2.firstname is null
as you can see, the sql is much the same as a normal query but with the addition of the IN clause providing the location of the file, the file type etc.