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
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
Tags: model, VBA, Windows