Excel 2010 Macro Count the Workbooks in Folder
This function searches the current directory and its subfolders, counting all Excel macro workbook files (XLSM) or just ones starting with a string of letters:
NumFilesInCurDir (LikeText, Subfolders)
The arguments are:
- LikeText – (Optional) A string value to search for must include an asterisk (*) such as Mr*
- Subfolders - (Optional) True to search subfolders, False (default) not to
This function is a recursive function that calls itself until a specific condition is met; in this case until all subfolders are processed.
Function NumFilesInCurDir(Optional strInclude As String = “”, _
Optional blnSubDirs As Boolean = False)
Dim fso As FileSystemObject
Dim fld As Folder
Dim fil As File
Dim subfld As Folder
Dim intFileCount As Integer
Dim strExtension As String
strExtension = “XLSM”
Set fso = New FileSystemObject
Set fld = fso.GetFolder(ThisWorkbook.Path)
For Each fil In fld.Files
If UCase(fil.Name) Like “*” & UCase(strInclude) & “*.” & _
UCase(strExtension) Then
intFileCount = intFileCount + 1
End If
Next fil
If blnSubDirs Then
For Each subfld In fld.Subfolders
intFileCount = intFileCount + NumFilesInCurDir(strInclude, True)
Next subfld
End If
NumFilesInCurDir = intFileCount
Set fso = Nothing
End Function
An example of using this function:
Sub CountMyWkbks()
Dim MyFiles As Integer
MyFiles = NumFilesInCurDir(“MrE*”, True)
MsgBox MyFiles &“ file(s) found”
End Sub