Application.FileSearch in VBA 2007

[Richard M. Hartman]

The very useful FileSearch method has been removed. The easiest way to
replace /most/ uses is with the FileSystemObject ... however, FileSearch had
a .SearchSubFolders attribute that was very useful ... is there a good way
to replicate this, or do I just have write a recursive routine using the
FileSystemObject to build the list?

 

[Gary''s Student]

Hi Richard:

See:

http://www.cpearson.com/Excel/RecursionAndFSO.htm

for an example

 

[Richard M. Hartman]

Pretty much the approach I took. Is there any way to do filename wildcards
the way filesearch did? This is what I've got:

' recursive file find to replace Application.FileSearch which is no longer
' available in Office 2007
' to do: separate "name" and "ext" patterns for search
Public Function FillFileList(path As String, pattern As String, recurse As
Integer, ByRef c As Collection) As Integer
Dim fso As FileSystemObject
Dim count As Integer
Dim path2 As String
Dim found As File
Dim child As Folder
Dim cd As Folder
Dim fname As String
Dim idx As Variant ' Long
Dim metacount As Integer

Debug.Print "FillFileList: " & path & ", " & pattern

Set fso = New FileSystemObject
Set cd = fso.GetFolder(path)
count = 0
metacount = 1

If Not cd Is Nothing Then
' add files to list
For Each found In cd.Files
metacount = metacount + 1
fname = found.Name
idx = 1
If Len(pattern) > 0 Then
idx = InStr(1, fname, pattern, vbTextCompare)
End If
If idx > 0 Then
c.Add found.path
count = count + 1
End If
Next

Debug.Print "pattern matched " & count & " of " & metacount & "
files in " & path

' recurse into subfolders
If recurse <> 0 Then
For Each child In cd.SubFolders
path2 = child.path
count = count + FillFileList(path2, pattern, recurse - 1, c)
Next
End If
Else
End If



FillFileList = count
End Function