vba-loop through subfolders

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
In what way isn't it working?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
How many folders do you have in the d:\neha directory?
 

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
You could replace the Exit Sub with End
 

ER_Neha

Board Regular
Joined
Jul 25, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,167
Messages
5,640,539
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top