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.
- 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.
- None known.
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.
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.
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
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: