+ Reply to Thread
Results 1 to 7 of 7

Recursively Count Files in Folders and Sub-Folders

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    chicaog, il
    MS-Off Ver
    Excel 2003
    Posts
    4

    Recursively Count Files in Folders and Sub-Folders

    Hi,
    I am using excel 2007 and have not been able to find a solution to my problem on any forum or internet source. I have a Parent directory that contains several folders and these folders may or may not contain subfolders aswell. I would like have the 2nd level folder names copied to a worksheet and in the adjacent column the number of files contained within those folders (which may or maynot contain subfolders). Any help would be greatly appreciated Thanks in advance!
    Folder Structure
    Parent : C:\Test
    Second Level Folders: C:\Test\test1
    C:\Test\test2
    Second Level Folders: C:\Test\test2\subtest2-1
    C:\Test\test2\subtest2-2
    C:\Test\test3

    Worksheet Result
    Column C Column D
    test1 5
    test2 10 (this value would include files found in the subfolders)
    test3 3

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,475

    Re: Recursively Count Files in Folders and Sub-Folders

    Take a look at this page on Chip Pearson's site:

    http://www.cpearson.com/excel/foldertreeview.aspx

    This actually lists all the files and folders in a tree view, so you should be able to amend it to perform a count of them.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    chicaog, il
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Recursively Count Files in Folders and Sub-Folders

    This is involving creating a userform and much more complicated than I want to get into. I don't want to list all the folders only want to list the Second level folders and the number of files they contain. I tried to download his workbook and kept running into errors anyway couldn't get it to work even after insert the Microsoft TreeForm control etc...

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626
    I don't think that is really recursive, you know the level you want to go to.

    All you need is to find and count the subfolders of the main folder (level 1) then for each of those folders find the no of subfolders (level 2) and count them.
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    02-06-2013
    Location
    chicaog, il
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Recursively Count Files in Folders and Sub-Folders

    I want to count the files contained in the folders, not the folders themselves. And it could be any number of folders and sub-folders or not it is not a constant existence of sub-folders. Some Second level folders contain sub-folders others don't.

  6. #6
    Registered User
    Join Date
    02-06-2013
    Location
    chicaog, il
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Recursively Count Files in Folders and Sub-Folders

    I have this code, but it lists every single folder down to the last level and its file count int he next column. I only want to list the second level folders with a TOTAL of all the files contained within it.


    Sub findAllFiles()
    Dim objFSO As Object
    Dim strFolder, intFileCount

    strFolder = "C:\Test"

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    i = 0
    Call CountFiles(strFolder, i)

    End Sub


    Sub CountFiles(strFolder, i)
    Dim objFolder, objFiles, SubFolder

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(strFolder)
    Set objFiles = objFolder.Files

    'record file count
    i = i + 1
    Range("A" & i) = strFolder & " Contains " & objFiles.count & " files"

    'record number of jpegs
    n = 0
    For Each file In objFolder.Files
    If UCase(file.Name) Like "*.JPG" Then
    n = n + 1
    End If
    Next file
    Range("B" & i) = n & " of these are jpegs"

    'iterate subfolders, if any
    For Each SubFolder In objFolder.subfolders
    Call CountFiles(SubFolder.Path, i)
    Next


    End Sub

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626
    It's the level I said was known, not the no of subfolders.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1