Counting excel files or pdf files in a folder

[Hiport]

Hi, is there a way to get a count of the number of excel files or pdf files in a folder, at the moment i have to manually count the number of files but if I have 100 + files its very time consuming

 

[John_w]

Try this:

Public Sub Tester()
    MsgBox Count_Files("F:\temp\excel", "*.xls")        'Change to suit
End Sub

Private Function Count_Files(folder As String, matchingFilename As String) As Long

    Dim filename As String
    
    If Right(folder, 1) <> "\" Then folder = folder & "\"
       
    Count_Files = 0
    filename = Dir(folder & matchingFilename, vbNormal)
    While Len(filename) <> 0
        Count_Files = Count_Files + 1
        filename = Dir()  ' Get next matching file
    Wend

End Function

 

[Hiport]

Hi there, the code is not flexible, is there a way where i can open a dialogue box and i can choose the folder? This will be used by my team so i can ask them to change the file extension every time they want to use it

Try this:

Public Sub Tester()
    MsgBox Count_Files("F:\temp\excel", "*.xls")        'Change to suit
End Sub
 
Private Function Count_Files(folder As String, matchingFilename As String) As Long
 
    Dim filename As String
 
    If Right(folder, 1) <> "\" Then folder = folder & "\"
 
    Count_Files = 0
    filename = Dir(folder & matchingFilename, vbNormal)
    While Len(filename) <> 0
        Count_Files = Count_Files + 1
        filename = Dir()  ' Get next matching file
    Wend
 
End Function

 

[Hiport]

*bump*

 

[John_w]

You could incorporate this 'browse for folder' code - http://www.vbaexpress.com/kb/getarticle.php?kb_id=284 - and pass the returned folder to Count_Files().