Count the Number of Workbooks in a Directory : Workbook « Excel « VBA / Excel / Access / Word






  1. VBA / Excel / Access / Word
  2. Excel
  3. Workbook

Count the Number of Workbooks in a Directory

 
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








Related examples in the same category

1.Center workbook
2.Create a new workbook
3.Creates a new workbook and adds it to the collection, reads the number of workbooks into a variable, and selects all worksheets in the active workbook:
4.Get workbook format
5.To close all open workbooks, use the Close method of the Workbooks collection object.
6.To close a single workbook, use the Close method of the Workbook object. The Close method accepts three optional arguments (SaveChanges, FileName, and RouteWorkbook).
7.To save a workbook from a VBA program, use either the Save methods of the Workbook object.
8.To select the last Workbook object in the collection
9.Activate work book
10.Use a String rather than an index value to reference a workbook
11.Save workbook and close
12.The Workbooks property is a member of the Application object and returns a reference to the Workbook object specified by the index value given in the parentheses.
13.Open workbook by name
14.Close a workbook and save it
15.Create a workbook and save it as a new file
16.The distinction between the Workbook and Window objects lies in an additional method that can be used to create a Window object
17.Open a workbook and then size it to fit just within the application window
18.Use Application object to save a workbook as a new file
19.Add a new workbook and save it
20.arranges the open workbooks in a tiled configuration
21.maximizes any workbook when it is activated
22.places the username in the footer of each sheet printed:
23.Check Whether a Workbook Is Open
24.Check Whether a Sheet in an Open Workbook Exists
25.Controlling Worksheet Visibility