Excel VBA check and display number of file in folder

Question

I have the following module to check the number of files contained in a folder and display a messagebox with the with the number of files:

Sub CheckFiles(strDir As String, strType As String)

    Dim file As Variant, i As Integer
    strDir = ThisWorkbook.Path & "\Source\"

    If Right(strDir, 1) <> "\" Then strDir = strDir & "\"
    file = Dir(strDir & strType)
    While (file <> "")
        i = i + 1
        file = Dir
    Wend
    MsgBox i
End Sub

Files to look for (in separate module):

   Call CheckFiles("", "File1*.xlsx")
    Call CheckFiles("", "File2*.xlsx")

What I want to do is to only display messagebox if the number of files for File1 is not excaly 3 and the number of files for File2 is not excaly 2. This is what I'm having trouble doing? How can this be acheived?

Add the number as a third parameter of the sub. Also, If Right(strDir, 1) <> "\" Then strDir = strDir & "\" is unnecessary, since you already have an ending backslash from strDir = ThisWorkbook.Path & "\Source\". — BigBen
– BigBen, Commented Aug 20, 2019 at 13:35

Mikku · Accepted Answer · 2019-08-20 13:44:11Z

1

Add the ChckNum as Third Parameter in the Subject and pass it in the Call Statement

Try:

Sub CheckFiles(strDir As String, strType As String, chknum As Integer)

    Dim file As Variant, i As Integer
    strDir = ThisWorkbook.path & "\Source\"

    If Right(strDir, 1) <> "\" Then strDir = strDir & "\"
    file = Dir(strDir & strType)
    While (file <> "")
        i = i + 1
        file = Dir
    Wend

    If i <> chknum Then MsgBox i


End Sub

And

  Call CheckFiles("", "File1*.xlsx", 3)
  Call CheckFiles("", "File2*.xlsx", 2)

edited Aug 20, 2019 at 13:44

answered Aug 20, 2019 at 13:36

Mikku

6,7043 gold badges19 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA check and display number of file in 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