The Windows Script Host Object Model has many useful properties and methods for dealing with files, folders, disks, etc. One common use is for searching for a file in one or more sub-folders. The example function below illustrates how to use the FileSystemObject along with the Folders, Folder, Files, and File objects to iterate through sub folders to find a particular file.

To use this example in your macros you must add a reference to the Windows Script Host Object Model library. To do this select Tools > References, then scroll toward the bottom of the list and select Windows Script Host Object Model.

'
'This function requires a reference to:
'Windows Script Host Object Model (C:\Windows\System32\wshom.ocx)"
'
Public Function FindFile(ByVal strFileName As String, _
ByVal strStartPath As String, ByRef strFoundPath) As Boolean
  Dim objFSO As FileSystemObject
  Dim objFiles As Files
  Dim objFile As File
  Dim objFolders As Folders
  Dim objFolder As Folder

  strFoundPath = ""

  'Set new instance if File System Object
  Set objFSO = New FileSystemObject

  'Get all sub folders in the starting folder
  Set objFolders = objFSO.GetFolder(strStartPath).SubFolders

  'Iterate through all sub folders
  For Each objFolder In objFolders

   'Get all of the files in this sub folder
    Set objFiles = objFolder.Files

   'Iterate through files in this sub folder
    For Each objFile In objFiles

     'If we find the file store the path name and exit
      If StrComp(strFileName, objFile.Name, vbTextCompare) = 0 Then
        strFoundPath = objFSO.BuildPath(strStartPath, objFolder.Name)
        FindFile = True
        Exit Function

      End If
    Next
  Next

  FindFile = False
End Function