vba-loop through subfolders

[ER_Neha]

How to loop through folders and their sub folders in vba. I tried using this code but its not working for me. my folder variable gets the correct name i.e. "d:\neha" but my variable sf for subfolder is not working

Sub ShowFolderList()
    Dim fso, folder, f1, sf
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("d:\neha")
    Set sf = folder.subfolders
    For Each f1 In sf
       If f1.name = "n1" Then
        MsgBox " found"
        End If
       
    Next
   
End Sub

 

[Fluff]

In what way isn't it working?

 

[ER_Neha]

In what way isn't it working?

Hello Fluff. No its not working. no error. nothing. the code also runs for just once i.e. for loop is running only once as the subfolder variable sf is null

 

[Fluff]

How many folders do you have in the d:\neha directory?

 

[ER_Neha]

4-5 subfolders eg d:\neha\new1\new2\new3\new4\new5\resultfolder

 

[Fluff]

SubFolders only looks at folders in the parent directory, it does not look at the entire directory structure. To do that you need to use a recursive search like

Sub ListFiles()
   Dim fso As Object
   Dim StartFldr As Object
   Dim StartPth As String
   
   StartPth = "D:\neha"
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set StartFldr = fso.GetFolder(StartPth)
   Call RecursiveFolder(fso, StartFldr, True)
   
End Sub

Sub RecursiveFolder(fso As Object, Fldr As Object, IncludeSubFolders As Boolean)
   Dim F As Object
   Dim SubFldr As Object
   
   For Each F In Fldr.subfolders
   Debug.Print F.Name
      If F.Name = "n1" Then
         MsgBox "Found"
       Exit Sub
      End If
   Next F
   If IncludeSubFolders Then
      For Each SubFldr In Fldr.subfolders
         Call RecursiveFolder(fso, SubFldr, True)
      Next SubFldr
   End If
End Sub

 

[ER_Neha]

SubFolders only looks at folders in the parent directory, it does not look at the entire directory structure. To do that you need to use a recursive search like

Sub ListFiles()
   Dim fso As Object
   Dim StartFldr As Object
   Dim StartPth As String
 
   StartPth = "D:\neha"
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set StartFldr = fso.GetFolder(StartPth)
   Call RecursiveFolder(fso, StartFldr, True)
 
End Sub

Sub RecursiveFolder(fso As Object, Fldr As Object, IncludeSubFolders As Boolean)
   Dim F As Object
   Dim SubFldr As Object
 
   For Each F In Fldr.subfolders
   Debug.Print F.Name
      If F.Name = "n1" Then
         MsgBox "Found"
       Exit Sub
      End If
   Next F
   If IncludeSubFolders Then
      For Each SubFldr In Fldr.subfolders
         Call RecursiveFolder(fso, SubFldr, True)
      Next SubFldr
   End If
End Sub

Hello Fluff, This code works the way I wanted to but there is one testing error- suppose
1. the parent folder of the file containing n1 , has two folders then which also has subfolder then the exit sub doesn't actually exiting but it comes to the step "next subfldr" inside the if-function.
I want to end the sub as soon as I get my desired folder

for e.g. d:\neha\new1\new2\n1
in new1 there are two folders new2 and new3 then if it loops through new2 first and finds my file n1 then it should exit the procedure and should not loop through new3

new3 ( new3 contains f1) should be after new2 in the file new1 because if new3 is before new2 then the process is smooth and the file name inside new3 wont be shown.
but using this code, as the procedure loops through new2 & new3 it finds my file n1 in new2 but it again prints the filename inside new3

 

[ER_Neha]

My immediate window is giving me this answer

new1
new2
new3
n1
f1


and also it is looping through f1 & & n1, i.e. even after giving me the name of f1 file , it keeps looping 3 times inside the last for loop before exiting. (used f8 to see the loop)

 

[Fluff]

You could replace the Exit Sub with End

 

[ER_Neha]

You could replace the Exit Sub with End

Did not work... after EXIT sub next step comes to next subfolders and loops inside the last FOR loop

thereby printing the other subfolders

and if END sub is used then compile error occurs suggesting: block if without end if

also i tried using the timer function and it takes 1.5 approx. seconds for the whole task.

Kindly Fluff please help