Count Files in a directory (All file types only, using FileScriptingObject)

Ease of Use


Version tested with

97, 2003 

Submitted by:

Ken Puls


This procedure will count all files in a directory using the File Scripting Object 


This function can count the total number of files in a directory. One potential use is to use the total to generate a percentage of files evaluated for a progress meter. NOTE: It may be advisible to test if the folder exists if the result returned from this function is 0 files. (If a folder is not found, the result will be 0.) This can be done with VBA's FolderExists method, which can be found in the VBA help files. 


instructions for use


Option Compare Text Option Explicit Function CountFiles(Directory As String) As Double 'Function purpose: To count all files in a directory Dim fso As Object, _ objFiles As Object 'Create objects to get a count of files in the directory Set fso = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFiles = fso.GetFolder(Directory).Files If Err.Number <> 0 Then CountFiles = 0 Else CountFiles = objFiles.Count End If On Error GoTo 0 End Function Sub TestCount() 'To demonstrate the use of the CountFiles function Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") If fso.folderexists(Selection) Then MsgBox "I found " & CountFiles(Selection) & " files in " & Selection, _ vbOKOnly + vbInformation, CountFiles(Selection) & " files found!" Else MsgBox "Sorry, but I can't find the folder: " & vbCrLf & Selection _ & vbCrLf & "Please select a cell that has a valid" & vbCrLf & _ "folder name in it!", vbOKOnly + vbCritical, "Error!" End If End Sub

How to use:

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

Test the code:

  1. In a worksheet cell, enter the following: =CountFiles("C:\Windows")... or use another valid directory
  2. If the file is working correctly, you should see a count of all files in the specified directory
  3. The function can also be called from VBA (eg MyVariable = CountFiles("C:\Windows")

Sample File:

CountFilesViaFso.zip 8.66KB 

Approved by mdmackillop

This entry has been viewed 185 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express