VBA/Excel/Access/Word/File Path/FileSearch

File files with Application.FileSearch

 
Option Compare Database
Option Explicit
Sub findFile()
   With Application.FileSearch
      .NewSearch
      .LookIn = "c:\VBA"
      .FileName = "mydb.mdb"
      .SearchSubFolders = True
      If .Execute() > 0 Then
         Debug.Print  .FoundFiles(1) & " was found"
      Else
         Debug.Print "The file is not found."
      End If
   End With
End Sub

Find a file

 
Sub findFile()
   With Application.fileSearch
        .NewSearch
        .LookIn = "c:\"
        .FileName = "mydb.mdb"
        .SearchSubFolders = True
            If .Execute() > 0 Then
               MsgBox "The file " & .FoundFiles(1) & " was found"
             Else
                MsgBox "The file is not found"
            End If
    End With
End Sub

Find a file with wildcard character

 
Sub findFile()
Dim i As Integer
   With Application.fileSearch
        .NewSearch
        .LookIn = "c:\"
        .FileName = "*.mdb"
        .SearchSubFolders = True
            If .Execute() > 0 Then
               For i = 1 To .FoundFiles.Count
                 Debug.Print .FoundFiles(i)
               Next i
            End If
    End With
End Sub

if a particular file exists, and False if not

 
Function FileExists(fname) As Boolean
    With Application.FileSearch
        .NewSearch
        .Filename = fname
        .Execute
        If .FoundFiles.Count = 0 Then _
          FileExists = False _
          Else FileExists = True
    End With
End Function

PropertyTests

 
Sub SetPropTests()
    Dim pt As PropertyTests
    Dim I As Integer
    Set pt = Application.fileSearch.PropertyTests
    With pt
        For I = .Count To 1 Step -1
            .Add name:=" Last Modified", _
                Condition:=msoConditionOnOrAfter, _
                Value:=DateAdd("d", -7, Date)
        Next I
    End With
End Sub

searches the My Documents directory and its subdirectories for all XLS files that contain the text budget.

 
Sub FindFiles()
    With Application.fileSearch
        .NewSearch
        .LookIn = "C:\My"
        .SearchSubFolders = True
        .TextOrProperty = "textInFile"
        .MatchTextExactly = True
        .FileName = "*.xls"
        .Execute
        For i = 1 To .FoundFiles.Count
            Debug.Print .FoundFiles(i)
        Next i
    End With
End Sub

The FileSearch Object

 
Sub fileSearch()
    Dim fs As fileSearch
    Dim numFiles As Long
    Set fs = Application.fileSearch
    With fs
        .NewSearch
        numFiles = .Execute
    End With
End Sub

The FoundFiles Collection Object

 
Sub found()
    Dim ff As FoundFiles
    Dim fs As fileSearch
    Dim I As Integer
    Set fs = Application.fileSearch
    Set ff = fs.FoundFiles
    With ff
        For I = 1 To ff.Count
            Debug.Print ff.Item(I)
        Next I
    End With
End Sub

Use Application.FileSearch to find files

 
Sub findFiles()
   Dim i As Integer
   
   With Application.FileSearch
      .NewSearch
      .LookIn = "c:\VBA"
      .FileName = "*.mdb"
      .SearchSubFolders = True
      If .Execute() > 0 Then
         For i = 1 To .FoundFiles.Count
            Debug.Print .FoundFiles(i)
         Next i
      End If
   End With
End Sub