Count Files with Specific File Name in Specific Folder

Question

I will first describe the definitions of my code, next define my problem and finally display my current code.

DEFINITIONS:

I am trying to create a looped function that will count the number of files with a specific/defined name within a folder with a specific/defined name.

Column AE defines the Folder Path ("Folder"). Column AH defines the File Name ("ExcelFN").

Cell AI1 defines how many rows this function should loop through (AI1 = "x").

I expect the function to (1) cycle through x number of rows, (2) count how many files with defined FileName (FileName = cell "AH & i") are found within defined Folder (FolderPath = cell "AE & i"), then (3) place the number of files into cell "AI & i", and finally (4) move onto the next row "i" and repeat prior steps.

PROBLEM:

My PROBLEM is that my code is placing 1, 2, 3, 4, 5, etc...all the way down AI - As a note, I do not have consecutively larger file counts as I move down the list, as should be obvious that one cannot save multiple files with the same name.

My guess is that my code is not refreshing NumFiles to zero before counting the number of files of the next row and just adding the current rows' file count to the previous's rows' file count.

CODE:

FINALLY - here is my code:

Sub CountFiles()

    Sheets("Sheet1").Activate

    Dim i As Integer
    Dim x As Integer
    Dim Folder As String
    Dim ExcelFN As String
    Dim NumFiles As Integer

    x = Sheets("Sheet1").Range("AI1").Value '====>> Define Number of Rows <<====

    For i = 2 To x

        Folder = Sheets("Sheet1").Range("AE" & i).Value & "\"
        ExcelFN = Sheets("Sheet1").Range("AH" & i).Value

        Filename = Dir(Folder & ExcelFN & "*" & ".xlsm")

        While Filename <> ""
            NumFiles = NumFiles + 1
            Filename = Dir()
        Wend

        Sheets("Sheet1").Range("AI" & i) = NumFiles

    Next i
End Sub

Jordan · Accepted Answer · 2016-12-20 17:10:47Z

1

Just reset the value of NumFiles at the beginning of your loop:

For i = 2 To x

    NumFiles = 0 '<-- Reset count

    Folder = Sheets("Sheet1").Range("AE" & i).Value & "\"
    ExcelFN = Sheets("Sheet1").Range("AH" & i).Value

    Filename = Dir(Folder & ExcelFN & "*" & ".xlsm")

    While Filename <> ""
        NumFiles = NumFiles + 1
        Filename = Dir()
    Wend

    Sheets("Sheet1").Range("AI" & i) = NumFiles

Next i

answered Dec 20, 2016 at 17:10

Jordan

4,5442 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Count Files with Specific File Name in Specific Folder

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related