Excel

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

Ease of Use

Easy

Version tested with

97, 2003 

Submitted by:

Ken Puls

Description:

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

Discussion:

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. 

Code:

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 188 times.

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