Is it possible to link excel files which will be created in future to one excel file?

Question

I have a folder with excel files named "purchase sheet", for every week we are creating new file with it's number and dates. I need to create one excel file, let's say "Real time copy", which will update new records in real time from all these files. For example, right now it's linked with file "Purchase sheet wk 40 01.05.24-08.05.24 - if you put something new in this file it automatically updates another one, but when the weeks ends, I need to create new file "Purchase sheet wk 41 09.05.24-16.05.24", is it possible to automatically(probably using Visual Basic) add new sheet on the "Real time copy" and link whatever will be added on wk 41 to a new sheet? Or I can create all sheets upfront, but I need them to be linked as soon as file was created. Or it will be easier to do it with access database?

I tried to create new sheets manually and put links upfront with changed names of the files, as they are known already, but it is asking to open the file I'm referring to, so I need somehow to add a check if this file exists and if it does, then link all information.