vba count # of files in folder

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
622
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try this:
Code:
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
 
Upvote 0
Try like this

Code:
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
 
Upvote 0
Hi,

Try this UDF.

Code:
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
 
Upvote 0
SOLVED Re: vba count # of files in folder

Thanks so much folks for replys.
 
Upvote 0
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,
 
Upvote 0
Re: SOLVED Re: vba count # of files in folder

Code:
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).
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top