How to use VBA script to count files/subfolders using a range from WB for the root folder
Category: [Excel] Demo Available
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.
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.