Recursive File Search

[juan4412]

I know you can use this to search the directory listed, but how can you search sub-directories as well? And one caveat -- ignore (or not open) any directory that is like *Temp


Public Sub SearchDirectory()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "L:\Excel"
MyFile = Dir(MyFolder & "\*.xlsx")
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile
MyFile = Dir
Debug.Print MyFile
Loop
End Sub

[snb]

Sub M_snb()
msgbox createobject("wscript.shell").exec("cmd /c Dir L:\Excel\*. /b/s").stdout.readall
End Sub

[Kenneth Hobs]

As you wanted, a folder like \Temp1 would be processed but not \xTemp, \Temp, etc.


Sub SearchDirectory()
Dim MyFolder As String, v As Variant, a() As String
Dim fso As Object, f As Object

MyFolder = "L:\Excel"

Set fso = CreateObject("Scripting.FileSystemObject")

a() = Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _
"""" & MyFolder & """" & " /b/s/ad").stdout.readall, vbCrLf)
a(UBound(a)) = MyFolder

For Each v In a()
If Not v Like "*\*Temp" Then
For Each f In fso.GetFolder(v).Files

'Your open code to replace debug.print line
'Debug.Print v, f
If Right(f, 4) = "xlsx" Then
Debug.Print v, f
End If

Next f
End If
Next v
End Sub