Search files in folder based on search string

**Leith Ross** · 09-21-2008, 02:11 PM

Hello djvice,

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

Sincerely,
Leith Ross

**djvice** · 09-21-2008, 02:18 PM

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.

**Leith Ross** · 09-21-2008, 03:40 PM

Hello djvice,

This macro will start listing files in the specified directory that match the account number input by the user. The list starts on Sheet1 A1. You can change these if you need to. They are marked in red.

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\Owner\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 <> ""
       FileName = Dir()
       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
     Loop
     
End Sub

Sincerely,
Leith Ross