Count Files in a folder

[blossomthe2nd]

Hi Guys

I have a found Code that will count files in a folder - I wish to count XLS files in this folder S:\Business Support\CGB\CGB\Jav\Jav Templates Received

Can anyone advise how to amend this query ?

Thanks

Private Function CountFiles(strDirectory As String, Optional strExt As String = "*.*") As Double
'Author          : Ken Puls (www.excelguru.ca)
'Function purpose: To count files in a directory.  If a file extension is provided,
'   then count only files of that type, otherwise return a count of all files.
    Dim objFso As Object
    Dim objFiles As Object
    Dim objFile As Object
    'Set Error Handling
    On Error GoTo EarlyExit
    'Create objects to get a count of files in the directory
    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objFiles = objFso.GetFolder(strDirectory).Files
    'Count files (that match the extension if provided)
    If strExt = "*.*" Then
        CountFiles = objFiles.Count
    Else
        For Each objFile In objFiles
            If UCase(Right(objFile.Path, (Len(objFile.Path) - InStrRev(objFile.Path, ".")))) = UCase(strExt) Then
                CountFiles = CountFiles + 1
            End If
        Next objFile
    End If
EarlyExit:
    'Clean up
    On Error Resume Next
    Set objFile = Nothing
    Set objFiles = Nothing
    Set objFso = Nothing
    On Error GoTo 0
End Function

 

[CharlesChuckieCharles]

Good point p45cal

muddled up with filedialog.. and of course FSO has no filter function

Just to do it to death

as old as this is it will count any file filter in a directory

'strsearch = "c:\"  or "h:\somefiles\*ff*.xls"
Public Function CountFiles2(strSearch As String) As Long
Dim bb As String
Dim ct As Long
On Error Resume Next
bb = Dir(strSearch)
While bb <> ""
    ct = ct + 1
    bb = Dir
Wend

CountFiles2 = ct

End Function

 

[blossomthe2nd]

Hi guys

Im sorry i cant get this working I have copied Jon Von ders code into a new workbook, n tool bar Tools - Macros , VBA , then inserted module - pasted code in . pressed saved , returned to worksheet and used

=CountifFiles("S:\Business Support\CorporateGB\CGB\Javelin\Jav Templates Received\Updated")

( I copied the pathe direct from window )

Can anyone help , sorry I just seem to be missing something

Thanks

 

[Jon von der Heyden]

This:

=CountifFiles("S:\Business Support\CorporateGB\CGB\Javelin\Jav Templates Received\Updated")

should give you a count of all files in that directory.

This:

=CountifFiles("S:\Business Support\CorporateGB\CGB\Javelin\Jav Templates Received\Updated","*.xls*")

should give you a count of all excel workbook files in that directory.

What result did you get and what result do you expect?