Searching Sub-folders
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
