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.

  Valid = FileName Like "####-##### (*"

Here is the revised code...

Sub ListFileNames()

  Dim FileName As String
  Dim FilePath As String
  Dim Valid As Boolean
  Dim Wks As Worksheet
  
    R = 1    'Starting Row
    Set Wks = Worksheets("Sheet1")
    FilePath = "C:\Documents and Settings\Admin.ADMINS\My Documents\*.xls"
    
InputAcct:
     Acct = InputBox("Please Enter an Account number.")
       If Acct = "" Then Exit Sub
         Valid = Acct Like "#####"
         If Not Valid Then
           MsgBox "Please Enter a 5 digit Account number."
           GoTo InputAcct
         End If
       
     FileName = Dir(FilePath)
     
     Do While FileName <> ""
       Valid = FileName Like "####-##### (*"
         If Valid Then
           If Mid(FileName, 6, 5) = Acct Then
             Wks.Cells(R, "A") = FileName
             R = R + 1
           End If
         End If
       FileName = Dir
     Loop
     
End Sub

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