Macro to List folder and count sub-folder one level down

[cjouban]

I have searched and cannot find a way of doing this. I need to list the folders and count the subfolders within that folder, but stop there. I don't want to count the sub-folders sub-folder.

An output like this would be great

Folder	Sub Count
2018	52
2017	1005
2016	1235
2015	1450
2014	900

<tbody>
</tbody>

Thanks in advance for any help

 

[cjouban]

Bump. I'm still struggling with this. Any help would be much appreciated.

 

[John_w]

Try this macro. Change the startFolderPath to the full path of the folder containing the 2018, 2017, etc. folders.

Public Sub Count_Subfolders()
    
    Dim startFolderPath As String
    Dim FSO As Object
    Dim startFolder As Object, subfolder As Object
    Dim r As Long
    
    startFolderPath = "C:\path\to\start\folder"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set startFolder = FSO.GetFolder(startFolderPath)
    
    With ActiveSheet
        .Cells.ClearContents
        .Range("A1:B1").Value = Array("Folder", "Subfolder count")
        r = 1
        For Each subfolder In startFolder.SubFolders
            r = r + 1
            .Cells(r, 1).Value = subfolder.Name
            .Cells(r, 2).Value = subfolder.SubFolders.Count
        Next
    End With
        
End Sub

 

[cjouban]

Works Perfect. Thank you.