PDA

View Full Version : Solved: File Count in Directory



parttime_guy
03-28-2009, 06:59 PM
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
03-28-2009, 08:03 PM
If you have a list of folder paths in column A and are using a Mac, this should do what you want.
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

parttime_guy
03-28-2009, 09:04 PM
Hi Mike,

Thanks for taking interest - I tried the code but... :banghead: , 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 - : pray2:

Thanks and Best regards

mikerickson
03-28-2009, 10:47 PM
I don't know Windows folder structure well enough to advise you.

mdmackillop
03-29-2009, 03:18 AM
You can get basic code from the Help Files under FileStstemObject

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



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.



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

parttime_guy
03-29-2009, 07:14 PM
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
04-04-2009, 07:48 PM
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
04-04-2009, 09:43 PM
A tweak of mdmackillop's code might go something like:
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

parttime_guy
04-05-2009, 08:12 PM
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
04-06-2009, 06:02 AM
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.

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