Given folder path in Column A, get file count of all sub-folders and files and put that number in Column B

[humerick]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Path[/TD]
[TD]# of files in folder/subfolders[/TD]
[/TR]
[TR]
[TD]Z:\Windows\User[/TD]
[TD]139[/TD]
[/TR]
[TR]
[TD]Z:\System\files[/TD]
[TD]1213[/TD]
[/TR]
[TR]
[TD]Z:\Windows\Temp[/TD]
[TD]5600[/TD]
[/TR]
[TR]
[TD]Z:\Data\user\jamie\files[/TD]
[TD]125[/TD]
[/TR]
</tbody>[/TABLE]


Can someone help me with some VBA code that will read down through Column A and starting at the path in that cell, get a count of all files and sub-folders under the path and report that number in Column B?

Thank you in advance for any help on this!

 

[VBA Geek]

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=Count_All_Files(<font color="Blue">A1</font>)</td></tr></tbody></table></td></tr></table><br />

Dim objFso As Object
Function Count_All_Files(ByVal sPath As String) as long 
Dim objFldr As Object
If objFso Is Nothing Then Set objFso = CreateObject("Scripting.FileSystemObject")
Count_All_Files = objFso.GetFolder(sPath).Files.Count
For Each objFldr In objFso.GetFolder(sPath).SubFolders
    Count_All_Files = Count_All_Files + Count_All_Files(objFldr.Path)
Next objFldr
End Function

 

[humerick]

That worked out great! Thank you, VBAGeek!