Solved: File Count in Directory

[parttime_guy]

Hi All,

I am trying to find the File count in a Directory

Column A – should have the Folder Name
Column B – should have the number of sub-folder’s
Column C – should have to Total count of number of files (all files included in the sub-folders and in folder path should be included in the count)

Kindly help.

Thanks and Best regards

[mikerickson]

If you have a list of folder paths in column A and are using a Mac, this should do what you want.
[VBA]Sub trial()
Dim oneCell As Range
With ThisWorkbook.Sheets("Sheet1").Range("A:A")
For Each oneCell In Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
oneCell.Offset(0, 1).Resize(1, 2).Value = FolderInfoFromPath(CStr(oneCell.Value))
Next oneCell
End With
End Sub

Function FolderInfoFromPath(FolderPath As String) As Variant
Dim scriptToRun As String
Dim returnedValues As String

scriptToRun = "tell application ""Finder"" " & vbLf & "try" & vbLf
scriptToRun = scriptToRun & " set myFolder to container " & Chr(34) & FolderPath & Chr(34) & vbLf
scriptToRun = scriptToRun & "return ((count of folders of myfolder) as text) & "","" & (count of items in entire contents of myfolder)" & vbLf
scriptToRun = scriptToRun & "end try" & vbLf & "end tell"
On Error Resume Next
returnedValues = MacScript(scriptToRun)
On Error GoTo 0
If returnedValues <> vbNullString Then
FolderInfoFromPath = Array(Val(Left(returnedValues, InStr(returnedValues, ","))) _
, Val(Mid(returnedValues, InStr(returnedValues, ",") + 1)))

End If
End Function

[/VBA]

[parttime_guy]

Hi Mike,

Thanks for taking interest - I tried the code but... , I need is list of folder and their respective count of files in the next column.
Iam not using a Mac.

Could you give me an alternate solution plz -

Thanks and Best regards

[mikerickson]

I don't know Windows folder structure well enough to advise you.

[mdmackillop]

You can get basic code from the Help Files under FileStstemObject
[vba]
Sub ShowFolderList(folderspec)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.SubFolders
For Each f1 in fc
s = s & f1.name
s = s & vbCrLf
Next
MsgBox s
End Sub

[/vba]

To get all sub and sub-sub folders to need a recursive loop. I do get some problems with hidden/system files, so treat results with caution.
You could include a couple of lines to list all files/folders for test purposes.


[vba]
Option Explicit
Dim fs
Dim FldCnt
Dim FilCnt
Dim f, f1, fc
Sub GG()
Set fs = CreateObject("Scripting.FileSystemObject")
Range("A2") = "Folders"
Range("A3") = "Files"
ShowFolderList Range("A1")
End Sub

Sub ShowFolderList(folderspec)
Set f = fs.GetFolder(folderspec)
Set fc = f.subfolders
On Error Resume Next
For Each f1 In fc
FilCnt = FilCnt + f1.Files.Count
FldCnt = FldCnt + f1.subfolders.Count
Range("B2") = FldCnt
Range("B3") = FilCnt
ShowFolderList f1
Next
End Sub


[/vba]

[parttime_guy]

Dear MD,

I tried the second code, it gives a total number of folders and files.
When I run the code the first time - the results show. But when I run the code again - for the second time, the totals doubles, it does not refresh - it should give the same results.

I am very new to vba and do not have any idea about writing codes, I just will have to wait for help to come along.

Thanks MD for showing some interest.

Best regards

[parttime_guy]

Dear All,

After much search I found this free tool on the net

http://www.nirsoft.net/utils/folrep.html

It has solved my problem....but could this be possible through vba.

Thx-n-BR

[Kenneth Hobs]

A tweak of mdmackillop's code might go something like:
[VBA]Dim fs As Object, f1 As Object, fc As Object, f As Object
Dim FldCnt As Long
Dim FilCnt As Long

Sub GG()
Range("A2") = "Folders"
Range("A3") = "Files"
ResetFolderList
ShowFolderList Range("A1").Value
End Sub

Sub ResetFolderList()
FilCnt = 0
FldCnt = 0
Set f1 = Nothing
Set fc = Nothing
Set fs = CreateObject("Scripting.FileSystemObject")
End Sub

Sub ShowFolderList(folderspec)
Range("B2").Value = 0
Range("B3").Value = 0
Set f = fs.GetFolder(folderspec)
Set fc = f.subfolders
If f1 Is Nothing Then
FilCnt = FilCnt + f.Files.Count
FldCnt = FldCnt + f.subfolders.Count
End If
'On Error Resume Next
For Each f1 In fc
FilCnt = FilCnt + f1.Files.Count
FldCnt = FldCnt + f1.subfolders.Count
ShowFolderList f1
Next
Range("B2").Value = FldCnt
Range("B3").Value = FilCnt
End Sub

[/VBA]

[parttime_guy]

Dear Kenneth,

Thanks for the code (the refresh problem is solved)

I am trying to achieve the following:

Something like a report having the - Total Folder/File count in a Directory – or selected path


The List should be in MS Excel format having the following details


Column A – should have the Folder Name (assuming I have 10 folders in the selected path – Column A should list the names of all the 10 folders)


Column B – should have the count of sub-folder’s (this column should only give the count of sub-folders for each of those 10 folder – if available)


Column C – should have to Total count of number of files (all files included in the sub-folders and in folder path should be included in the count)

Kindly help.

[Kenneth Hobs]

Not sure that I know what "Excel format" is.

ReCountMyFoldersAndFiles() gets the parent folder names from A2 and down or from your block selection if you selected more than 1 cell in 1 column.

The routine uses the SpeedUp Modules routines found in the commented KB article's link. You can comment out the SpeedOn and SpeedOff if speed is of no concern.

[vba]Dim fs As Object, f1 As Object, fc As Object, f As Object
Dim FldCnt As Long, FilCnt As Long

Sub ReCountMyFoldersAndFiles()
Dim cell As Range, parentRange As Range
On Error GoTo EndNow
'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
SpeedOn
'Lets user select 2 or more column cells
If Selection.Count > 1 And Selection.Columns.Count = 1 Then
Set parentRange = Selection
Else: Set parentRange = Range("A2", Range("A" & Rows.Count).End(xlUp))
End If
'Iterate each cell from A2 down or each cell in selection
For Each cell In parentRange
If IsEmpty(cell) Then
cell.Offset(0, 1).Value = vbNullString
cell.Offset(0, 2).Value = vbNullString
Else
FolderFileCount cell
End If
Next cell
EndNow:
SpeedOff
Cells(parentRange.Row, parentRange.Column).Select
End Sub
Sub FolderFileCount(pRange As Range)
ResetFolderList
ShowFolderList pRange
FinalReset
End Sub
Sub FinalReset()
FilCnt = 0
FldCnt = 0
Set f1 = Nothing
Set fc = Nothing
Set fs = Nothing
End Sub

Sub ResetFolderList()
FilCnt = 0
FldCnt = 0
Set f1 = Nothing
Set fc = Nothing
Set fs = CreateObject("Scripting.FileSystemObject")
End Sub

Sub ShowFolderList(rFolderspec As Range)
Dim folderspec As String
folderspec = rFolderspec.Value
rFolderspec.Offset(0, 1).Value = 0
rFolderspec.Offset(0, 2).Value = 0
Set f = fs.GetFolder(folderspec)
Set fc = f.subfolders
If f1 Is Nothing Then
FilCnt = FilCnt + f.Files.Count
FldCnt = FldCnt + f.subfolders.Count
End If
On Error Resume Next
For Each f1 In fc
FilCnt = FilCnt + f1.Files.Count
FldCnt = FldCnt + f1.subfolders.Count
ShowFolderList f1
Next
rFolderspec.Offset(0, 1).Value = FldCnt
rFolderspec.Offset(0, 2).Value = FilCnt
End Sub
[/vba]