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

< Back to Search results

 Category: [Excel]  Demo Available 

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




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.




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
        .Cells(j, 4).Value = k
      End If
  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.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to use VBA code to output multiple worksheets to separate workbooks
How to use VBA code to generate report based on criteria
How to use Excel VBA code to hide based on criteria
How to protect VBA source code from view


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)