Search files in folder based on search string

I have several XLS files in a folder. They are all name similarly:

2111-13252 (1-5-08)
2111-12345 (5-32-07)
1122-21935 (9-5-08)

Location - Account (Date)

In Excel, I want to be able to search for a file. Bring up a popup where the user types in the 5 digit account number [MID(7,5)]. Code searches all files for that account number. If it finds it, then create MsgBox with the entire filename.

So the user types in 21935 in the popup.
Code looks for these digits in the folder, based off characters 6 - 10 of the filenames in that folder.
When it finds it, display filename in a MsgBox, so in this case "1122-21935 (9-5-08).xls"

Thanks

Hello djvice,

Did you want to search a specific directory or multiple directories?

Sincerely,
Leith Ross

Single directory.

After pondering it some more, I think it would be best to list the filenames in Sheet1 under column A. That way, if there are more than 1 results, it can list all files with the search criteria.

Hmmm....im trying to run the code but it doesn't do anything.

Hello djvice,

Sorry about that, I made a typo in the Like operator for the file name comparison. It is missing an asterisk at the end.

Please Login or Register  to view this content.

Here is the revised code...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

You're a freakin' genius!

Hello djvice,

At the end of the loop, check if "R" = 1. R increments each time a file is found. If R = 1 then no files were found.

Sincerely,
Leith Ross