vba count # of files in folder

[zrx1200]

Hello,

I'm looking at being able to count the # of files in a folder that match a varible.

I only need the first portion of the filename. example filename is Apr 4 6 00 0.09 and I only need the Apr 4 portion which is the date.

I will then compare the current date (which is the varible) in code to the filename portion and count how many files are present.

The path for folder is constant.

Any thought folks?

Thanks,
Ralph

 

[John_w]

Try this:

Sub Count_Files()

    Dim compareDate As String
    Dim folder As String
    Dim filename As String
    Dim numFiles As Integer
    
    folder = "F:\Temp\Excel\"                   'CHANGE THE FOLDER TO SUIT
    
    compareDate = Format(Date, "mmm d")         'CURRENT DATE
    
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    numFiles = 0
    filename = Dir(folder & compareDate & " *")
    While filename <> ""
        numFiles = numFiles + 1
        filename = Dir()
    Wend
    
    MsgBox numFiles & " file(s) in " & folder & " matching " & compareDate
    
End Sub

 

[VoG]

Try like this

Sub CountFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\" & Format(Date, "mmm d") & "*.xls")
Do While MyFile <> ""
    j = j + 1
    MyFile = Dir
Loop
MsgBox j & " files found"
End Sub

 

[Krishnakumar]

Hi,

Try this UDF.

Function COUNTFILESIF(ByVal FolderName As String, ByVal Extn As String, Optional ByVal Criteria As String) As Long
    
    Dim FileName    As String
    Dim strExtn     As String
    Dim blnSkipCrit As Boolean
    
    If Right$(FolderName, 1) <> "\" Then FolderName = FolderName & "\"
    If Not CBool(Len(Dir(FolderName, vbNormal))) Then
        COUNTFILESIF = CVErr(xlErrNA)
        Exit Function
    End If
    Extn = LCase$(Replace(Extn, ".", ""))
    FileName = LCase$(Dir(FolderName & "*." & Extn))
    If Len(Criteria) Then
        Criteria = LCase$(Criteria)
    Else
        blnSkipCrit = True
    End If
    Do While Len(FileName)
        strExtn = LCase$(Mid$(FileName, InStrRev(FileName, ".") + 1))
        If strExtn Like Extn Then
            If Not blnSkipCrit Then
                If InStr(1, FileName, Criteria) Then
                    COUNTFILESIF = COUNTFILESIF + 1
                End If
            Else
                COUNTFILESIF = COUNTFILESIF + 1
            End If
        End If
        FileName = LCase$(Dir())
    Loop
    
End Function

use like

=COUNTFILESIF(A1,".xls*","April")

where A1 houses the folder path

HTH

 

[zrx1200]

SOLVED Re: vba count # of files in folder

Thanks so much folks for replys.

 

[JAnders]

Re: SOLVED Re: vba count # of files in folder

What edit would I need to make to change this to look for files that CONTAIN TEXT vs EQUAL to TEXT?
EX: The above function worked great when I had it look for however if I have 5 files "Results20190801a", "Results20190801b", "Results20190801c, "Results20190801d","Results20190801e"....I want to be able to have the criteria "Results20190801" and return 5 instead of 0.

Hopefully that made sense.

Thanks,

 

[xenou]

Re: SOLVED Re: vba count # of files in folder

Sub test()
    
    Dim fso As Object 'Scripting.FileSystemObject
    Dim fldr As Object 'Scripting.Folder
    Dim f As Object 'Scripting.File
    Dim lookFor As String
    
    
    lookFor = "Apr 4"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    With fso
        Set fldr = .GetFolder("C:\myTemp")
        For Each f In fldr.Files
            If f.Name Like "*" & lookFor & "*" Then
                Debug.Print f.Path
            End If
        Next f
    End With
    
End Sub

This always seems a little better than the clunky DIR calls from the VBA library (to me).

 

[JAnders]

Re: SOLVED Re: vba count # of files in folder

Thanks for the response xenou. I like it, however the "LookFor" would be a variable as it would reference a list of what I'm file names I'm searching for and return the count to the cell to the right of it.

 

[xenou]

Re: SOLVED Re: vba count # of files in folder

okay let us know if you are stuck getting it to work with a list instead of a single value.

 

[JAnders]

Re: SOLVED Re: vba count # of files in folder

yes, I'm a little stuck on that part.