Get a table from each Excel file in folder | speed up the process

Have you tried linking without the use of macro or VBA? Just link directly via a formula? I have a master sheet for tracking investments that links to multiple "source files" solely by using the FILTER function and pointing to the various source files. The source files, in my experience, do need to be open in order for the refreshed data to appear....but (also in my experience) the use of VBA or a macro often slows down the process. So whenever possible, I use Excel's built-in functions instead.