VBA - Pull Data from multiple closed Workbooks (if sheet names exist) using a date naming convention

[NGB82KS]

I've seen some code, but nothing that matches exactly what I'm looking to accomplish. I posed the question about this using indirect but apparently its not feasible.

I have a root folder called "Teachers" and under that folder, hundreds of subfolders for all the schools ( e.g. Teachers\School 1\; Teachers\School 2\; Teachers\School 3\; etc..) and within each of those folders are excel files with the following naming convention, "Report 2020 10 01.xlsx";"Report 2020 11 01.xlsx";"Report 2020 11 30.xlsx".

Is there a way to use a cell reference on my consolidated workbook (B2) with a date and use vb script to pull the information from each excel file if it exists from the subfolder that ends in that date from (B2) in my consolidate workbook? (e.g. \\Teachers\School 1\[Report 2020 11 01.xlsx]; Teachers\School 2\[Report 2020 11 01.xlsx]; Teachers\School 3\Report 2020 10 01.xlsx?)

Example:
Consolidated Workbook

	A	B	C	D
1
2	Date	2020 11 01
3
4	School 1	45
5	School 2	38

File \\Teacher\School 1\[Report 2020 11 01.xlsx]

	A	B	C	D
84
85			OROG Rating	45

File \\Teacher\School 2\[Report 2020 11 01.xlsx]

	A	B	C	D
84
85			OROG Rating	38

 

[NGB82KS]

Sorry, the sheet name in the workbooks (Report 2020 11 01.xlsx) are the default name "Sheet1".