|
|
|
|
|
|
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
Dim fso As Object, _
objFiles As Object
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()
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:
|
- 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 following: =CountFiles("C:\Windows")... or use another valid directory
- If the file is working correctly, you should see a count of all files in the specified directory
- 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.
|
|