How to iterate through all sub-folders and their sub-sub-folders and so on using vba to check a folder if it exist?

Question

I have below code to check if a folder exist on the predefined directory.

Option Explicit
Public xStatus As String


Sub Status()
Application.ScreenUpdating = False
Dim fso As Object
Dim folder As Object
Dim subfolders As Object
Dim subfolder1 As Object
Dim Rg As Range
Dim xCell As Range
Dim xTxt As String



xTxt = ActiveWindow.RangeSelection.Address
Set Rg = Application.InputBox("Please select city/cities to check production status!!!      ", "Lmtools", xTxt, , , , , 8)
If Rg Is Nothing Then
    MsgBox ("No cities selected!!!")
    Exit Sub
End If
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("D:\")
Set subfolders = folder.subfolders
For Each xCell In Rg
    If xCell.Value <> "" Then
        For Each subfolder1 In subfolders
            xStatus = subfolder1.path
            If xStatus Like "*?\" & xCell.Value Then
                Cells(xCell.Row, xCell.Column + 1).Value = "Completed"
                Cells(xCell.Row, xCell.Column + 2).Value = xStatus
                GoTo nextiteration
            Else
                Cells(xCell.Row, xCell.Column + 1).Value = "Ongoing"
            End If
        Next
    End If
nextiteration:
Next

Application.ScreenUpdating = True
End Sub

It works fine but only checks the sub-folders of "D:\" and not beyond that.

My folder could be present anywhere (either inside the sub-folders or their sub-folders or alongside "D:\"'s sub-folders.

my concern is how to iterate through all the folders.

You need to do this recursively. You should put your folder search code into a separate sub-function that can be called at each folder level. Check out [this question](https://stackoverflow.com/questions/30511217/optimize-speed-of-recursive-file-search-in-subdirectories) for an example. — SmrtGrunt, May 30 '19 at 13:10

score 0 · Accepted Answer · answered May 30 '19 at 20:24

I creted this a while back. Basically i used this to rename file in folders and sub-folders,

Option Explicit
Sub VersionRename()

Dim SelectedFolder As FileDialog
Dim T_Str As String
Dim FSO As Object
Dim RenamingFolder As Object, SubFolder As Object
Dim T_Name As String

Set SelectedFolder = Application.FileDialog(msoFileDialogFolderPicker)
    SelectedFolder.Title = "Select folder:"
    SelectedFolder.ButtonName = "Select Folder"
    If SelectedFolder.Show = -1 Then
        T_Str = SelectedFolder.SelectedItems(1)
    Else
        'MsgBox "Cancelled by user.", vbInformation
    Set SelectedFolder = Nothing
    Exit Sub
    End If

    Set SelectedFolder = Nothing

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set RenamingFolder = FSO.GetFolder(T_Str)
        File_Renamer RenamingFolder

    For Each SubFolder In RenamingFolder.SubFolders
        File_Renamer SubFolder
    Next

    Set SubFolder = Nothing
    Set RenamingFolder = Nothing
    Set FSO = Nothing

    MsgBox "Process completed!", vbInformation, Title:="Renaming Files"


End Sub
Private Sub File_Renamer(Folder As Object)

Dim File As Object
Dim T_Str As String
Dim T_Name As String
Dim PreVersionID As Variant
Dim NextVersionID As Variant 
Dim StringReplace As String

    PreVersionID = Application.InputBox("Input 1 if no version number otherwise input existing version number:", Type:=1)
    If PreVersionID = False Then Exit Sub
    NextVersionID = Application.InputBox("Input your next version number:", Type:=1)
    If NextVersionID = False Then Exit Sub


    T_Str = Format("_V" & NextVersionID)

    For Each File In Folder.Files
        T_Name = File.Name
        'Debug.Print T_Name
        If NextVersionID > 1 Then
            StringReplace = Replace(T_Name, "_V" & PreVersionID, "", 1, 3)
            'Debug.Print StringReplace
            File.Name = Left(StringReplace, InStrRev(StringReplace, ".") - 1) & T_Str & Right(StringReplace, Len(StringReplace) - (InStrRev(StringReplace, ".") - 1))
        Else
            File.Name = Left(T_Name, InStrRev(T_Name, ".") - 1) & T_Str & Right(T_Name, Len(T_Name) - (InStrRev(T_Name, ".") - 1))
        End If
    Next
End Sub

How to iterate through all sub-folders and their sub-sub-folders and so on using vba to check a folder if it exist?

1 Answers1