Mega Code Archive
Count the Number of Workbooks in a Directory
<![CDATA[
Function NumFilesInCurDir(Optional strInclude As String = "")
Dim myFileSystemObject As FileSystemObject
Dim fld As folder
Dim fil As file
Dim subfld As folder
Dim intFileCount As Integer
Dim strExtension As String
strExtension = "XLS"
Set myFileSystemObject = New FileSystemObject
Set fld = myFileSystemObject.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
For Each subfld In fld.SubFolders
intFileCount = intFileCount + NumFilesInCurDir(strInclude)
Next subfld
NumFilesInCurDir = intFileCount
Set myFileSystemObject = Nothing
End Function
Sub CountMyWkbks()
Dim MyFiles As Integer
MyFiles = NumFilesInCurDir("MrE*")
MsgBox MyFiles & " file(s) found"
End Sub
]]>