Scan All Workbooks in Folder For Specified Value

[ChuckRobert]

I need to scan hundreds of archived workbooks in a given folder to find a specified value on the "Radio" worksheet (in column B).
In the same row, coumn D is where it was used, Coumn E is the User Serial Number, and Column F is the User Name.

Ideally, I'd like to create a simple excel form where I could enter the radio number I want to search for in cell A1, and 1 macro button to start the search.

Ideally, I'd like the macro to build a list on the simple excel form that consists of:

Workbook name in which the value was found (Column A), Location (Coumn B), User Serial Number (Column C), and User Name in Column D, then repeating this in the next row for the next workbook checked...

I may be able to work this out on my own, but the experts on this forum have been outstanding, and I expect some may have used something similar I could work with. Thanks!

 

[hrlngrv]

Would there be multiple records in each workbook? Would the first record (if possibly multiple) or the only record (if never multiple) always be in the same cells? Best way to approach this depends on your answers to these questions.

If there were hundreds of such files, you'd be FAR BETTER OFF extracting the data from ALL FILES <b>ONCE</b> and storing the data in another workbook. Then add a worksheet to that workbook for interactive querying.

 

[ChuckRobert]

There may be 1, and only 1, instance of that radio number in column B of the "Radio" worksheet in every archived workbook within that folder.

The radio number I'm looking for may not always be in the same row of column B, however, as some have come and gone over the years...

 

[hrlngrv]

The most general approach would be using a macro in a workbook different from any of these others. The macro would open each of these files in sequence, copy the intersection of each file's Radio worksheet's UsedRange and column B into the workbook with the macro, then close the file and move on to the next one. You'd want to add the workbook name in a column next to the extracted column B values. This sort of macro could take a long time to run, maybe a whole weekend.

 

[ChuckRobert]

That's exactly what I was interested in trying, and hope that someone may have some starter code I may be able to adapt. Otherwise, this project will sit on the back burner for a while...
There are about 3500 archived workbooks...
Thanks anyway.