Count the Number of Workbooks in a Directory

This function searches the current directory, and its subfolders if you want, counting all Excel workbook files or just the ones starting with a string of letters. NumFileslnCurDir (LikeText, Subfolders)

The arguments are

LikeText—Optional, a string value to search for, must include "*,"; for example: Mr* Subfolders—Optional; True to search subfolders, False (default) not to.

Function NumFilesInCurDir(Optional LikeText As String, _

Optional Subfolders As Boolean = False) With Application.FileSearch .NewSearch

If Len(LikeText) > 0 Then

.Filename = LikeText 'sets a wildcard filename to search for End If

.FileType = msoFileTypeExcelWorkbooks 'specifies to look for workbooks .LookIn = CurDir 'specifies to look in the current directory .SearchSubFolders = Subfolders 'sets whether subfolders should be searched .Execute

NumFilesInCurDir = .FoundFiles.Count End With End Function

Here is an example of using this function:

Sub CountMyWkbks()

Dim MyFiles As Integer

MyFiles = NumFilesInCurDir("MrE*", True)

MsgBox MyFiles & " file(s) found"

End Sub

0 0

Post a comment