Search Subfolders, then pass array of files found to be processed?

I have the following example code:

Sub SearchFiles2()
'does not search subfolders
Dim arrFiles() As String, ffile As String
Dim i As Integer
Dim sPath As String

sPath = BrowseForFolder(0, "Select source folder:")
MsgBox sPath
If sPath = "" Then Exit Sub
If Right$(sPath, 1) = "\" Then
  sPath = Mid$(sPath, 1, Len(sPath) - 1)
End If

ffile = Dir$(sPath & "\*.xls", vbArchive)
Do While ffile <> ""
    ReDim Preserve arrFiles(i)
    arrFiles(i) = sPath & "\" & ffile
    i = i + 1
    ffile = Dir$()
Call ProcessFiles(arrFiles)
End Sub

Sub ProcessFiles(arr() As String)
Dim i As Integer
Dim R As Range, c As Range
Dim wb As Workbook
Dim sPath, ffile As String
For i = 0 To UBound(arr) - 1
Set wb = Application.Workbooks.Open(arr(i))
 Set R = wb.ActiveSheet.Range("m7:m300")
 For Each c In R
     If c.Offset(0, -5).Value = 0 Or c.Offset(-1, -5).Value = 0 Then
     End If
 Next c
 For Each c In R
     If IsError(c) Then
     ElseIf c.Value <= 1 And c.Value > 0 Then
         If c.Value <= 1 And c.Value > 0 And c.Offset(0, 1).Value >= 300 Then
             c.Interior.ColorIndex = 6
             c.Offset(0, 1).Interior.ColorIndex = 6
             c.Interior.ColorIndex = 6
         End If
     End If
 Next c
 For Each c In R
     If IsError(c) Then
     ElseIf c.Value <= 0.25 And c.Value > 0 Then
             c.Borders.LineStyle = xlContinuous
             c.Borders.Weight = xlThick
             c.Borders.ColorIndex = 1
     End If
 Next c
 For Each c In R
     If IsError(c) Then
     ElseIf c.Value <= 0.5 And c.Value > 0 Then
         If c.Value <= 0.5 And c.Value > 0 And c.Offset(0, 1).Value >= 300 Then
             c.Interior.ColorIndex = 3
             c.Offset(0, 1).Interior.ColorIndex = 3
         End If
     End If
 Next c
 For Each c In R
     If IsError(c) Then
     ElseIf c.Value <= 0.5 And c.Value > 0.25 Then
         If c.Value <= 0.5 And c.Value > 0.25 And c.Offset(0, 1).Value >= 300 Then
             c.Interior.ColorIndex = 33
         End If
     End If
 Next c
 wb.Close True
 Set wb = Nothing

Next i
End Sub

Which searches a folder of choosing and then fills an array of file locations, which are in turn passed to the ProcessFiles function.  However, if there are subfolders that contain the data I want process, the SearchFiles function will not find them.  I want to be able to select the top level folder and let the script batch process through all the subfolders and then pass the array to whatever processing script I choose.  The ProcessFiles is just one such example of processing code I want to accomplish!

Help and advice?

Who is Participating?
dmangConnect With a Mentor Commented:
try using the filesearch object (this sample from MS Word VBA

dim strFNames() as string

With Application.FileSearch
      .LookIn = strPath
      .SearchSubFolders = True      'include all subfolders
      .FileType = msoFileTypeAllFiles 'can be change as needed to correct enum              
      If .Execute() > 0 Then
        For i = 1 To .FoundFiles.Count
             redim preserve strfnames(i)
             strfnames(i - 1)=  .FoundFiles(i)
        Next i
      end if

end with
jmfairchildConnect With a Mentor Commented:
There is a great example at the site below which I believe does exactly what you want and more:

I would paste the code directly here, but the comments in the code state:

' Distribution: You can freely use this code in your own
'               applications, but you may not reproduce
'               or publish this code on any web site,
'               online service, or distribute as source
'               on any media without express permission.

and I didn't have time to get permission (lol).

- Jim
Hi zoomer777,
This old question (QID 20558836) needs to be finalized -- accept an answer, split points, or get a refund.  Please see for information and options.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

-->Split between dmang and jmfairchild

Please leave any comments here within the next seven days.


EE Cleanup Volunteer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.