Excel VBA- FSO List subfolders

By Ajeet Kumar

EXCEL VBA TO LIST ALL SUB FOLDERS OF A SELECTED FOLDER

Option Explicit

Sub pListingSubFolders()

    Dim FSO As Object
    Dim objTextStream As Object
    Dim strPath As String
    Dim ReturnV As Double
    
    On Error GoTo labelERR
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Title = "Select A Folder"
        .Show
        strPath = .SelectedItems(1)
    End With
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set objTextStream = FSO.CreateTextFile(strPath & "\ABC.bat")
    
    With objTextStream
        .WriteLine "CD/"
        .WriteLine "CD """ & strPath & """"
        .WriteLine "DIR/a:d/b>LIST.xls"
        .Close '' File must be closed before using Shell command
    End With
    Shell strPath & "\ABC.bat", vbHide
    
labelERR:
    If Err.Number <> 0 Then
        MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description
    End If
End Sub