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

NGB82KS

Board Regular
Joined
Nov 7, 2019
Messages
50
Office Version
  1. 2016
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
ABCD
1
2Date2020 11 01
3
4School 145
5School 238

File \\Teacher\School 1\[Report 2020 11 01.xlsx]
ABCD
84
85OROG Rating45

File \\Teacher\School 2\[Report 2020 11 01.xlsx]
ABCD
84
85OROG Rating38
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

NGB82KS

Board Regular
Joined
Nov 7, 2019
Messages
50
Office Version
  1. 2016
Sorry, the sheet name in the workbooks (Report 2020 11 01.xlsx) are the default name "Sheet1".
 

Watch MrExcel Video

Forum statistics

Threads
1,122,581
Messages
5,597,018
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top