How do I include subfolders in my folders.file loop?

[deevooneh]

I have a macro to perform an operation on all files in a given folder, but I want to also run the macro on the excel files in subfolders. How can I do that? Here's my code so far.

Sub LoopFoldersTest()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Dim oFSO<o:p></o:p>
Dim Folder As Object<o:p></o:p>
Dim Files As Object<o:p></o:p>
Dim file As Object<o:p></o:p>
<o:p></o:p>
Set oFSO = CreateObject("Scripting.FileSystemObject")<o:p></o:p>
<o:p></o:p>
Set Folder = oFSO.GetFolder("C:\My documents\Test folder")<o:p></o:p>
<o:p></o:p>
For Each file In Folder.Files<o:p></o:p>
<o:p></o:p>
If file.Type Like "*Microsoft Excel*" Then<o:p></o:p>
Workbooks.Open Filename:=file.Path<o:p></o:p>
<o:p></o:p>
' Turn off workbook macro that prompts yes/no for recalculate
Application.EnableEvents = False<o:p></o:p>
<o:p></o:p>
'<<<<< run macro you want here on Activeworkbook<o:p></o:p>
Run "test_operation"<o:p></o:p>
<o:p></o:p>
' Turn back on workbook macro that prompts yes/no for recalculate<o:p></o:p>
Application.EnableEvents = True<o:p></o:p>
<o:p></o:p>
ActiveWorkbook.Close SaveChanges:=True<o:p></o:p>
<o:p></o:p>
End If<o:p></o:p>
<o:p></o:p>
Next file<o:p></o:p>
Set oFSO = Nothing<o:p></o:p>
<o:p></o:p>
End Sub

Can anyone please help? Thank you,

Arad

 

[John_w]

Try this:

Sub LoopFoldersTest()
    
    Dim oFso As Object
    
    Set oFso = CreateObject("Scripting.FileSystemObject")
    ProcessFolder oFso, "C:\My documents\Test folder"
    
    Set oFso = Nothing

End Sub

Private Sub ProcessFolder(oFso, folderPath As String)

    Dim Folder As Object
    Dim File As Object
    Dim Subfolder As Object
    
    Set Folder = oFso.GetFolder(folderPath)
    
    For Each File In Folder.Files
    
        If File.Type Like "*Microsoft Excel*" Then
            Workbooks.Open Filename:=File.Path
            
            ' Turn off workbook macro that prompts yes/no for recalculate
            Application.EnableEvents = False
            
            '<<<<< run macro you want here on Activeworkbook
            Run "test_operation"
            
            ' Turn back on workbook macro that prompts yes/no for recalculate
            Application.EnableEvents = True
            
            ActiveWorkbook.Close SaveChanges:=True
        
        End If
           
    Next

    'Process subfolders
    
    For Each Subfolder In Folder.subfolders
        ProcessFolder oFso, Subfolder.Path
    Next

End Sub