Total file count in folder and subfolders

Roto36 · Mar 23rd 2016

This code works great for a target folder count. But some of my target folders now have hundreds of sub folders. How do I make it search a folder and subfolders and return a total file count? Need it to search all file types.

Code

Sub sample()


    Dim FolderPath As String, path As String, count As Integer
    FolderPath = "C:\Share\2015\My_Project\HwySign"


    path = FolderPath & "\*"


    Filename = Dir(path)


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


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

Display More

**S O** · Mar 23rd 2016

Re: Total file count in folder and subfolders

Try this, fastest way is using command prompt.

Code

FolderPath = "C:\Share\2015\My_Project\HwySign"


Dim fCount As Long


fCount = UBound(Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR """ & FolderPath & "\*.*"" /S /B /A:-D").StdOut.ReadAll, vbCrLf), ".")) + 1


MsgBox Format(fCount, "#,00") & " files were found."

A black box will appear for a minute while it searches for files - this is normal.

Roto36 · Mar 23rd 2016

Re: Total file count in folder and subfolders

Yes it works pretty good, but I have no use for the msg box that pops up. I need the results to post into a specified cell.

Roto36 · Mar 29th 2016

Re: Total file count in folder and subfolders

Got this to work searching only subfolders, It will be good enough.

Code

Sub R1Photos()
    Dim StrFile As String
    Dim objFSO, destRow As Long
    Dim mainFolder, mySubFolder
        Set objFSO = CreateObject("Scripting.FileSystemObject")
    mFolder = "H:\Region_1\2015\ATR Site Photos and Inventory Verif" ' <<<< to be changed
    Set mainFolder = objFSO.GetFolder(mFolder)
    StrFile = Dir(mFolder & "*.*")
    Do While Len(StrFile) > 0
    Loop
    
    For Each mySubFolder In mainFolder.SubFolders
    StrFile = Dir(mySubFolder & "\*.*")
    Do While Len(StrFile) > 0
        count = count + 1
        StrFile = Dir
    Loop
        Range("E28").Value = count
    'MsgBox count & " : files found in folder"
    Next
End Sub

Display More

Participate now!

Share