Consulting

Results 1 to 4 of 4

Thread: VBA with counting files

  1. #1
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    2
    Location

    VBA with counting files

    Hi

    So in work we have this folder with multiple folders in it. Each of them have different number of various files. We need to know specifically how each folder has files. In excel we have a table with folder names. So in our code the Range D7: 14 are the exact location of the specific folder and the result should appear next to it (the number of files).

    The problem I am facing is that the first folder shows the correct number, but the second adds the files from the previous folder and so on. I cant see my mistake. Please help!

    Here is the code:

    Dim FolderPath As String, Path As String, count As Integer
    Set MyRange = Range("D7:14")


    For Each c In MyRange


    Path = c & "\*.xlsx"
    FileName = Dir(Path)
    Do While FileName <> ""
    count = count + 1
    FileName = Dir()
    Loop
    c.Offset(0, 1).Value = count


    Next c

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    732
    Location
    you never reset your count. it is inside your loop.

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,307
    Dim FolderPath As String, Path As String, count As Integer
    Set MyRange = Range("D7:14")    '<<< doubt this line'll work.
    
    For Each c In MyRange
      count = 0
      Path = c & "\*.xlsx"
      Filename = Dir(Path)
      Do While Filename <> ""
        count = count + 1
        Filename = Dir()
      Loop
      c.Offset(0, 1).Value = count
    Next c
    but check it gives the right count.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    2
    Location
    Thank You! It helped very much

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
  •