Summing up values in one column from multiple files

Hi - Here again, another brick wall!

I'm trying to sum up column 'O' from all the files that are in the week '43' folder, so that they appear in the active file, next to "week 43" under "TOTAL SHORTS" (B2). I plan on repeating this for the other weeks in column A (44, 45, 46....) of which they have their own folders.

I've attached a screen shot of the folder that contains files i am trying to sum data from 'wk 43'.
I've attached a copy of the active sheet i want the results to go to: 'example active sheet'.

I started to write this with the formula in B2:

=SUM('T:\Automated\Archive\43\[090718.xlsx]Sheet1'!$O:$O,'T:\Automated\Archive\43\[100718.xlsx]Sheet1'!$O:$O,'T:\Automated\Archive\43\[110718.xlsx]Sheet1'!$O:$O)

This was working, but then i realised it would take a very long time for to do this for the remainder days in the week, and then do the same for everyweek, and that the user of this sheet would have to update this formula on a daily basis which they cannot do. I also get prompted to update the file with a pop-up window (screenshot attached)

The values need are all in column O, and they always will be.

Is there something i can do to the formula or a macro i can perform that searches & sums from the same column, 'O', across multiple files
And can i do anything to capture any future files that are added.

Please can you help?

Zach

I'm open to rearranging folders or making additional sheets to make this possible and friendly to update