How to Need VBA script to count files/subfolders using a range from WB for the root folder

How to use VBA script to count files/subfolders using a range from WB for the root folder

Requirement:

The user needs to look in a lot of individual network folders and get a count of all files and/or subfolders within that specific folder.

The user would like to loop through a range of folder addresses as defines in a column from excel then write the file and/or subfolder count to another range of cells next to the address list.

Basically column A1 through A2000 list a set of network paths that I need to scan for files and subfolders and get a count of each written into column B (file count) and C (subfolder count). A1 is \\\folder\subfolder\ so B1 should show count of files here and C1 should show count of subfolders here.

Solution:

Code:

Sub CountFilesAndFolders()
  Dim wsSht As Worksheet
  Dim FSO As Object, Folder As Object, SubFolder As Object
  Dim sFolder As String
  Dim j As Long, k As Long
 
  'assume the first sheet in this book contains the data
  Set wsSht = ThisWorkbook.Sheets(1)
 
  With wsSht
    'create the file system object
    Set FSO = CreateObject("Scripting.FileSystemObject")

    'loop through all the rows from 1 to last row in column A
    For j = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
      sFolder = .Cells(j, 1)
      'check if the folder exists
      If FSO.FolderExists(sFolder) <> False Then
        'set up the folder object
        Set Folder = FSO.GetFolder(sFolder)
        'write out the file and subfolder count
        .Cells(j, 2).Value = Folder.Files.Count      'Column B = file count, root folder
        .Cells(j, 3).Value = Folder.SubFolders.Count 'Column C = folder count
        k = 0
        'this is optional - can write out the total number of files in the sub-folders into Column D
        For Each SubFolder In Folder.SubFolders
          k = k + SubFolder.Files.Count
        Next
        .Cells(j, 4).Value = k
      End If
    Next
  End With
 
  'clean up
  Set FSO = Nothing
  Set Folder = Nothing: Set SubFolder = Nothing
  Set wsSht = Nothing
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by gijsmo.