VBA Express : Excel - Count Files in a directory (All file types or one specific type only, using FileSearch)

Excel

Count Files in a directory (All file types or one specific type only, using FileSearch)

Ease of Use

Easy

Version tested with

97, 2003

Submitted by:

Ken Puls

Description:

This function can be used to return a count of either all files in a directory, or only files matching a specific 3 digit extension supplied by the user.

Discussion:

This function can be used to count the number of files in a directory matching the users criteria, and that result could easily be passed to a progress meter. HOW IT WORKS: This function makes use of the Application.filesearch method to speed up the file counting loop process. Unlike other methods, where the extension of each and every file in a directory must be evaluated to see if it matches the user's choice, this method limits the number of files based on their msoFileType. For example, Excel files ending in .xls, .xla, .xlt (etc...) all belong to the msoFileTypeExcelWorkbooks, but word documents (among others) do not. So let's say you want to count all *.xls files in the directory only. By filtering to show msoFileTypeExcelWorkbooks only, you eliminate all the extraneous file types, meaning that you only have to loop through the excel files to count those with a .xls extension. (ie Less files mean less time counting!)

Code:

instructions for use

Option Compare Text Option Explicit Function CountFiles(Directory As String, Optional Ext As String = "All") As Double 'Function purpose: To count all the files in a directory 'Alternate purpose: To count all files in a directory with a specified file extension 'Method: If a file extension is supplied as an arguement, we can cut down on the list ' of files to filter through by calling only files that are in the same ' msoFileType group. (ie .doc and .dot files belong to the WordDocuments ' group, but not the ExcelWorkbooks group. This will allow us to count the ' number of files matching the extension more quickly, since we will only ' operate on files that belong to that group, not all groups. If no file ' extension is supplied (or the file does not belong to any specified group,) ' we can still resort to counting all files. Dim fs As Object, i As Integer 'Create the FileSearch object Set fs = Application.FileSearch 'If an empty string has been passed as the file extension, set it to "All" If Len(Ext) < 3 Then Ext = "All" With fs 'Set the directory to look in to the directory arguement supplied by the user .LookIn = Directory 'Determine the msoFileType group that the file extension belongs to. .FileType = GetMSOFileType(Ext) 'Execute the search .Execute If Ext = "All" Then 'If no file extension supplied, count all files in the directory CountFiles = .FoundFiles.Count Else 'If a file extension is supplied count the number of files in the 'filtered list which match the supplied extension For i = 1 To .FoundFiles.Count If Right(.FoundFiles.Item(i), 3) = Right(Ext, 3) Then _ CountFiles = CountFiles + 1 Next i End If End With 'Release the FileSearch object Set fs = Nothing End Function Function GetMSOFileType(FileExt As String) As Double 'Function purpose: To determine the msoFileType of a file extension 'Note: If a file extension does not exist in the list, the file type will default ' to AllFiles. More msoFileTypes can be found by looking up the "FileType Property" ' in the VBA help, or on Sheet2 of the example workbook Select Case Right(FileExt, 3) Case Is = "doc", "dot" 'Assign file type of msoFileTypeWordDocuments 'NOTE: msoFileTypeWordDocuments does not include "rtf" files GetMSOFileType = 3 Case Is = "xls", "xla", "xlt", "xlc", "xlm" 'Assign file type of msoFileTypeExcelWorkbooks 'NOTE: msoFileTypeExcelWorkbooks does not include "xll" or "xlw" files GetMSOFileType = 4 Case Is = "ppt", "pps", "pot" 'Assign file type of msoFileTypePowerPointPresentations GetMSOFileType = 5 Case Is = "mdb", "mde", "ade", "adp" 'Assign file type of msoFileTypeDatabases 'NOTE: msoFileTypeDatabases does not include "mda" files GetMSOFileType = 7 Case Is = "pub" 'If XL2002 or later, assign file type of msoFileTypePublisherFiles 'otherwise, assign file type of msoFileTypeAllFiles If Val(Application.Version) < 10 Then GetMSOFileType = 1 Else GetMSOFileType = 18 End If Case Is = "vsd", "vss", "vst" 'If XL2002 or later, assign file type of msoFileTypeVisioFiles 'otherwise, assign file type of msoFileTypeAllFiles 'NOTE: msoFileTypeVisioFiles does not include "vsw", "vdx", "vsx" or "vtx" files If Val(Application.Version) < 10 Then GetMSOFileType = 1 Else GetMSOFileType = 21 End If Case Is = "htm", "tml", "mht" 'If XL2002 or later, assign file type of msoFileTypeWebPages 'otherwise, assign file type of msoFileTypeAllFiles If Val(Application.Version) < 10 Then GetMSOFileType = 1 Else GetMSOFileType = 23 End If Case Else 'Assign file type of msoFileTypeAllFiles GetMSOFileType = 1 End Select End Function

How to use:

Copy above code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Test the code:

In a worksheet cell, enter the formula in the cell as =CountFiles([Directory Path],[ext]), where [Directory Path] is the full path to the files (eg. C:\Temp), and [ext] is an optional 3 character file type (eg. xls)
eg =CountFiles("C:\Temp","xls")
In VBA call the function in the same way

Sample File:

CountFilesViaFilesearch.zip 15.84KB

Approved by mdmackillop

This entry has been viewed 189 times.