Get file path from a cell, run a check to see if the file has a specific sheet if yes copy

Hello

I am trying to build a master purchase order spreadsheet, which will run a macro from a specific cell and check if the file has a sheet called extract if it has then it will copy a name range in the sheet which is also called extract and paste to the master spread sheet moving one column to the left each time.

So far I found a spreadsheet on another website that runs a macro to get the filepaths into individual cells in a spreadsheet, I have found code on here that opens the workbooks, but now I'm thinking as there are over 500 files each in a seperate folder that it will be quicker to do this with the workbooks closed. The code I have so far is

Sub Openfile()

Workbooks.Open Range("A1") & ".xlsm"

End Sub

Book2.xlsm

I need this code to loop throught the cells also.

I am fairly new to vba, is what im lookng for achievable?