In VBA you can easily loop through the files which match a particular wildcard,. For example, "Test*.xls". I suggest you look up how to use FileSystemObject. There's also the built-in Dir() function, which is simpler to use but provides less functionality.
You don't necessarily need to link them. Your VBA code can use Excel to open each file (do some searching, the use of Excel from VB/VBA has been covered quite a bit on TheScripts) and read the info from it.
As for renaming, that's also easy in VBA. It's as simple as Name "Test.xls" As "Test - Imported.xls" (of course you would normally be substituting variable names in there, not hard-coding the name).
|