count how many of files in each folder and subfolder then display separately

Question

I have obtained the filename and corresponding folder in column a and b.

How Can I count the number of files in each folder and subfolder?

these are the references:

count files in specific folder and display the number into 1 cel

http://software-solutions-online.com/excel-vba-find-and-list-all-files-in-a-directory-and-its-subdirectories/

I have done the following so far

Private Sub SelectFolder_Click()
Call GetAllFolders(x, strPath, objFso, intCountRows)
Call count
End Sub

The following is the counting part

Sub count()
Dim FolderPath As String, path As String, count As Integer
Dim Filename As String
Dim strPath As String

FolderPath = "D:\Users\Desktop\test"

Filename = Dir("")

Do While Filename <> ""
   count = count + 1
   Filename = Dir()
Loop

Range("C3").Value = count
'MsgBox count & " : files found in folder"
End Sub

How Can I count the number of files in each folder and subfolder? Thanks

score 4 · Accepted Answer · answered Jul 16 '16 at 00:30

4

GetFileCount will count all the files in a directory and the directory's sub-directories.

Range("C3").Value = getFileCount("D:\Users\Desktop\test")

Function getFileCount(localRoot, Optional fld, Optional count As Long) As Long
    Dim fso, f, baseFolder, subFolder, ftpFile, i

    Set fso = CreateObject("Scripting.Filesystemobject")

    If IsMissing(fld) Then
        Set baseFolder = fso.GetFolder(localRoot)
    Else
        Set baseFolder = fld
    End If

    count = count + baseFolder.Files.count

    For Each subFolder In baseFolder.SubFolders
        getFileCount localRoot, subFolder, count
    Next

    getFileCount = count
End Function

answered Jul 16 '16 at 00:30

Thanks Thomas, that's works for counting total number of files in folder. But what if I want to display the number of files in folder and subfolder seperately – Adam Law Jul 18 '16 at 02:56
Glad that I could help. – Jul 18 '16 at 02:58
Do you know how to display the number separately? – Adam Law Jul 18 '16 at 03:01
Would you want it in 1 cell? Or would you want the Folders to branch off into adjacent cells? – Jul 18 '16 at 03:04
I would like to show the number in different cells according to folders' directory. – Adam Law Jul 18 '16 at 03:09
This is confusing...lol. I'll get back to you tomorrow night. – Jul 18 '16 at 04:25

Rush Sina · Answer 2 · 2020-07-20T09:10:25.323

I hope this may help. I found this code:

'DEVELOPER: Ryan Wells (wellsr.com)
'DESCRIPTION: This macro counts the files in a folder and retuns the result in a msgbox
'INPUT: Pass the procedure a string with your directory path and an optional
' file extension with the * wildcard
'EXAMPLES: Call CountFilesInFolder("C:\Users\Ryan\")
' Call CountFilesInFolder("C:\Users\Ryan\", "*txt")

Private Sub CountFilesInFolder(strDir As String, Optional strType As String)

Dim file As Variant, i As Integer
If Right(strDir, 1) <> "\" Then strDir = strDir & "\"
file = Dir(strDir & strType)
While (file <> "")
    i = i + 1
    file = Dir
Wend
MsgBox "Num of files are " & i

End Sub

Then I modified a little:

Sub CountFiles()

Dim myValue As Variant
myValue = InputBox("What is the file path?")
Range("A1").Value = myValue '(Put the given value into A1)
myValue = InputBox("What is the second file path?")
Range("A2").Value = myValue '(Put the given value into A2)

Call CountFilesInFolder(Range("A1").Value)
Call CountFilesInFolder(Range("A2").Value)

End Sub

count how many of files in each folder and subfolder then display separately

2 Answers2

Linked

Related