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.


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)


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
   Call MsgBox ("There were no files found.")
End If

Key Properties and Methods

FileNamespecifies the name of the file to be found (wildcards can be used)
FoundFileReturns a FileSystem object that contains the names of the files that have been found
LookInSpecifies the directory to be searched
SearchSubFoldersReturns True if subdirectories are to searched
ExecutePerforms the search
NewSearchResets 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
      .LookIn = "C:\Temp\"
      .SearchSubFolders = False
      .FileType = msoFileType.msoFileTypeAllFiles
      .LastModified = msoLastModifiedToday
'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
      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

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext