Thanks for your prompt reply, cant put all files in 1 single folder. The team that owns data must split the files in separate folders to keep it organised, and i dont have permission to copy the files and place those in 1 single folder. they need to be like that only for eg:
and then every Period has 8 to 10 different xls files (in which I only need to combine the very first tab) as shown in my post above)
for eg; PERIOD 1 has 7 different files as shown below
and so on, is ther a workaround to achieve this, without disturbing the folder structure please.
Do you have all these folders in one folder? I mean you have a folder containing Period 1 folder, Period 2 folder, etc. Then read from this folder, it can read all the files in the sub folders, then you can try to locate items contains "Week" in name