Count number of Excel files in folder and subfolder

[Syedali]

Hi to all friends.
Count number of Excel files in folder and subfolder

 

[Luke M]

Copied from:
http://stackoverflow.com/questions/...r-and-subfolders-exlucing-folders-with-string

Function fCount(strPath)
    Dim fCnt As Integer
    fCnt = ShowFolderList(strPath)
    fCount = fCnt
End Function

Sub CntFiles()
    Dim strPath As String
    strPath = "A:\Asif\Answers\abc"
    ShowFolderList (strPath)
End Sub

Function ShowFolderList(Path)
    Dim fso, folder, subFlds, fld
    Dim tFiles As Integer

    tFiles = ShowFilesList(Path)

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(Path)
    Set subFlds = folder.SubFolders

    For Each fld In subFlds
        If fld.Name = "Entered" Then
            GoTo SkipFld:
        Else
            Path = fld.Path
            tFiles = tFiles + ShowFilesList(Path)
        End If
SkipFld:
    Next

    'MsgBox tFiles & " files"
    ShowFolderList = tFiles
End Function

Function ShowFilesList(folderspec)
   Dim fso, f, f1, fc, s
   Dim Cnt As Integer

   Set fso = CreateObject("Scripting.FileSystemObject")

   Set f = fso.GetFolder(folderspec)
   Set fc = f.Files

        For Each f1 In fc

            If GetAnExtension(f1) = "xls" Then
                Cnt = Cnt + 1
            Else

            End If

        Next

   ShowFilesList = Cnt
End Function

Function GetAnExtension(DriveSpec)
   Dim fso
   Set fso = CreateObject("Scripting.FileSystemObject")
   GetAnExtension = fso.GetExtensionName(DriveSpec)
End Function

Then, in your workbook, you can get the file count by writing a formula like:
=fCount("C:\My Documents\My Files")