• Count Files (with a specific extension or not) in a Folder

    Introduction:
    This macro can be used to count how many files exist in a folder and, if passed the file extension, can also be used to count only files of a specific type. (For example "xls" files.) I've actually published two articles along this vein at VBA Expresss, but this one uses the File System Object (some know it as the File Scripting Object, or just FSO,) to do it. The biggest reason for the re-write is that I needed to use this in an Excel 2007 instance, which does not support the FileSearch method.
    If you are looking for a version to count the files in a folder AND subfolders, then look here.

    Macro Purpose:
    • Count all files in a folder
    • Count all files of a specific type in a folder

    Examples of where this function shines:
    • When checking if you should process a folder for any reason, this can be used to quickly test if any files worth looking at exist inside.

    Macro Weakness(es):
    • None known.

    Versions Tested:
    This function has been tested with Excel 2007. Due to the use of the InStrRev function, it will not work in Excel 97. While I have not tested it with Excel 2000 and Excel 2002 (XP) without any modifications, I don't expect any issues with it. (If you find one, please post a comment at the end of the article.)

    VBA Code Required:
    • Place the following code in a standard module of the workbook you wish to use it in.

    Code:
    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
    How to use the code:
    • Call it from another routine, as shown using a FileDialog example below. (FileDialogFolderPicker may not work in Excel 2000.)
    • Please note that you should provide the full file path to the folder as the argument.
    • You may supply or ignore the trailing \ on a directory if you choose. It will work either way.

    Code:
    Sub Test()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Test the CountFiles function
        Dim flDlg As FileDialog
        Dim dblCount As Double
        Set flDlg = Application.FileDialog(msoFileDialogFolderPicker)
        flDlg.Show
        dblCount = CountFiles(flDlg.SelectedItems(1))
        Debug.Print dblCount
    End Sub
    References:
    More about the File System Object can be found here on MSDN.

    As mentioned above, this routine is based off works that I originally submitted as KB entries at VBAExpress. Those entries are:

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 2 Comments
    1. Lavalamp's Avatar
      Lavalamp -
      I've been using this on my PC with no problems, I've now got a Mac and I can't seem to make it work on here. Any tips?
    1. LQuintero's Avatar
      LQuintero -
      Hi Ken. Very good code.

      It seems all the charm is in there:


      Set objFso = CreateObject("Scripting.FileSystemObject") Set objFiles = objFso.GetFolder(strDirectory).Files

      In my case I need not only to count files but also to process each individual file after getting that number of files. Using a For Next loop. How can I index each file to do that? is there any other attribute to use?

      Thanks.
  • MVP Logo
  • Recent Forum Posts

    alansidman

    Best method to create training log?

    Follow up to Ali's suggestion. Here is a link that I often refer Access Template Lookers to examine. While it does not give you an actual DB, it does...

    alansidman Today, 05:54 AM Go to last post
    p45cal

    Need help with total monthly hours

    Enter times as follows: If it's 2:05 in the afternoon enter as 2:05 PM or 14:05.
    The attached should handle times across midnight, but check....

    p45cal Yesterday, 11:22 PM Go to last post
    dcope7

    Need help with total monthly hours

    I took the 12 out and if you key in 0:30 it row 12 it still subtracts from row 16 which is what it is supposed to do. I've tried every format I could...

    dcope7 Yesterday, 07:05 PM Go to last post
    p45cal

    Need help with total monthly hours

    It does not! it adds the value 12 and doesn't even look at row 12! In this context it adds 12 days (=288 hours). Barmy.
    Until you get formulae right...

    p45cal Yesterday, 06:02 PM Go to last post
    dcope7

    Need help with total monthly hours

    Ok row 16 calculates rows 11 and 15, plus it subtracts row 12 and yes I know I have it adding but if you actually add 0:30 in row 12 it will subtract...

    dcope7 Yesterday, 05:23 PM Go to last post