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

 

[Trevor G]

Adjust this line

If strExt = "*.*" Then

To

If strExt = "*.xls" Then

 

[p45cal]

have you tried calling the function with the optional second argument?
x=CountFiles("S:\Business Support\CGB\CGB\Jav\Jav Templates Received","xls")

(I suspect that Trevor G's suggestion may not give you the answer you want!)

 

[blossomthe2nd]

HI There

I cant get it to run at all , i'm brand new at this VBA, so I literally just opened a new workbook, went to tools, macros VBA, added a module and pasted the code in.

i've saved it but not sure how to make it run !!! Can youhelp p45cal,

sorry I know its probably very basic stuff

 

[CharlesChuckieCharles]

Change the key word Private to public

then in a cell on a worksheet

type

=countifles("S:\Business Support\CGB\CGB\Jav\Jav","*.xls*")

 

[p45cal]

Change the key word Private to public

then in a cell on a worksheet

type

=countifles("S:\Business Support\CGB\CGB\Jav\Jav","*.xls*")

a slight tweak, change:
=countifles("S:\Business Support\CGB\CGB\Jav\Jav","*.xls*")
to:
=countifles("S:\Business Support\CGB\CGB\Jav\Jav","xls")

 

[CharlesChuckieCharles]

"*.xls" will MISS files from 2007 onwards

 

[p45cal]

"*.xls" will MISS files from 2007 onwards

…will miss ALL files, as will "*.xls*"

 

[CharlesChuckieCharles]

Good point p45cal

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

 

[Jon von der Heyden]

Try:

Public Function CountifFiles(ByVal strPath As String, Optional ByVal strExtension As String = "*") As Long
    Dim strFileName As String
    Dim strPS As String: strPS = Application.PathSeparator
    
    strPath = Replace$(strPath & strPS, String(2, strPS), strPS)
    
    strFileName = Dir$(strPath & strExtension)
    
    Do While Len(strFileName) > 0
        CountifFiles = CountifFiles + 1
        strFileName = Dir$
    Loop
End Function

The following will return a count of all files in the directory:
=CountifFiles("C:\")

The following will return a count of all excel workbook files:
=CountifFiles("C:\","*.xls*")