Application.FileSearch

Removed in Office 2007
Office 97 introduced a FileSearch object (enhanced in 2002) that allows you to search and maintain files easily.
This allows you to search for files with a wide range of search criteria such as file type, file size, file location and date of last modification.
This object can be found in the Office library.

Application.FileSearch

The FileSearch object basically gives you all the functionality of the (File > Open) dialog box
You can use FileSearch instead of using the earlier Dir() function.

Types of Objects

FileTypes - In 97 and 2000 you could only specify a single FileType property. Office 2002 introduced a FileTypes collection that allows you to specify multiple file types.
FoundFiles collection - All the matching file names are placed in this collection.
PropertyTests -
SearchScopes collection - (Added 2002)
ScopeFolders - (Added 2002)
SearchFolders - (Added 2002)

Example

Dim iFileCount As Integer 
Dim objFileSearch As Variant 

Set objFileSearch = Application.FileSearch 
objFileSearch.LookIn = "C:\Temp\" 
objFileSearch.FileType = msoFileType.msoFileTypeExcelWorkbooks 

If objFileSearch.Execute(msoSortBy.msoSortbyFileName, _ 
                         msoSortOrder.msoSortOrderAscending) > 0 Then 
   For iFileCount = 1 To objFileSearch.FoundFiles.Count 

   Next iFileCount 
Else 
   Call MsgBox ("There were no files found.") 
End If

Key Properties and Methods

FileName	specifies the name of the file to be found (wildcards can be used)
FoundFile	Returns a FileSystem object that contains the names of the files that have been found
LookIn	Specifies the directory to be searched
SearchSubFolders	Returns True if subdirectories are to searched
Execute	Performs the search
NewSearch	Resets the FileSearch object to its default settings. All property values are retained after each search is run, and by using the NewSearch method you can selectively set properties for the next file search without manually resetting previous property values.

Any files found are placed in a FoundFiles collection

Private Sub SearchAFolder() 
Dim sfolderpath As String 
Dim sextension As String 
Dim objfilesearch As Variant 
Dim vobjfile As Variant 
Dim sfullname As String 
   
   Set objfilesearch = Application.FileSearch 
   
   With objfilesearch 
      .NewSearch 
      .LookIn = "C:\Temp\" 
      .SearchSubFolders = False 
      .FileType = msoFileType.msoFileTypeAllFiles 
      .LastModified = msoLastModifiedToday 
      .Execute 
      
      'for each control variable must be variant or object
      For Each vobjfile In .FoundFiles 
         sfullname = CStr(vobjfile) 
         If Right(sfullname, 3) = ".bmp" Then 
         End If 
      Next vobjfile 
      
   End With 
End Sub

List all the files in the root directory

Private Sub FileFinder() 
Dim objFile As Variant 
   With Application.FileSearch 
      .LookIn = "C:\" 
      .FileType = msoFileType.msoFileTypeAllFiles 
      .Execute 
      For Each objFile in .FoundFiles 
         Call MsgBox(objFile) 
      Next objFile 
   End With 
End Sub

Return most recent file from a folder

Dim sfullname As String 

   With Application.FileSearch 
      .LookIn = "C:\" 
      .FileName = "" 
      If .Execute(msoSortByLastModified, _ 
                  msoSortOrderDescending, True) > 0 Then 
          sfullname = .FoundFiles(1) 
      End If 
   End With