Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2016
    Posts
    3
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question List the folders in a directory count the files in the folders (VBA and Excel)

    The title says it all folks. I was trying to list all the folders in a certain directory and then in a column beside the list of folders give a count of the files in each folder on an excel spreadsheet). My files are listed but the count next to each folder is bogus. (it seems to be adding eight from the previous loops iteration.)

    Without further ado, my code:
    Code:
    Sub ListFolders()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objSubFolder As Object
    Dim i As Integer
    Dim count As Integer
    Dim pathos As String
    Dim FolderPath As String
    
    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Get the folder object
    Set objFolder = objFSO.GetFolder("C:\Dropbox")
    i = 1
    'loops through each file in the directory and prints their names and path
    For Each objSubFolder In objFolder.subfolders
        'print folder name
        Cells(i + 1, 11) = objSubFolder.Name
        'print count of files in subfolder
        FolderPath = "C:\Dropbox"
        pathos = FolderPath & "\*.*"
        Filename = Dir(pathos)
       Do While Filename <> ""
      
           count = count + 1
          
            Filename = Dir()
        
         Loop
         Cells(i + 1, 12).Value = count
        i = i + 1
    Next objSubFolder
    End Sub
    This is code is the result of web searches and personal conjecture not strictly my code. Please help
    Last edited by RetiredGeek; 2016-07-06 at 09:13. Reason: Added Code Tags

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,170
    Thanks
    47
    Thanked 978 Times in 908 Posts
    As you have already set the folder you should use it and not do a separate count.
    Code:
    For Each objSubFolder In objFolder.subfolders
    'print folder name
    Cells(i + 1, 11) = objSubFolder.Name
    'print count of files in subfolder
    Cells(i + 1, 12).Value = objSubFolder.Files.Count
    i = i + 1
    Next 'objSubFolder
    cheers, Paul

  3. The Following User Says Thank You to Paul T For This Useful Post:

    primevyl (2016-07-06)

Tags for this Thread

Posting Permissions

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