VBA自定义函数：统计指定扩展名的文件数量

' 目的: 统计文件夹中的文件数.
' 如果提供了文件扩展名, 则仅统计这种类型的文件
' 否则返回所有文件数.
Function CountFiles(strDirectory As String, Optional strExt As String = "*.*") As Double
 Dim objFso As Object
 Dim objFiles As Object
 Dim objFile As Object
 
 '设置错误处理
 On Error GoTo EarlyExit
 
 '创建对象以获取文件夹中的文件数
 Set objFso = CreateObject("Scripting.FileSystemObject")
 Set objFiles = objFso.GetFolder(strDirectory).Files
 '统计文件数 (如果提从则匹配扩展名)
 If strExt = "*.*" Then
   CountFiles = objFiles.Count
 Else
   For Each objFile In objFiles
     If UCase(Right(objFile.Path, (Len(objFile.Path) - InStrRev(objFile.Path, ".")))) = UCase(strExt) Then
         CountFiles = CountFiles + 1
     End If
   Next objFile
 End If
 
EarlyExit:
 '整理
 On Error Resume Next
 Set objFile = Nothing
 Set objFiles = Nothing
 Set objFso = Nothing
 On Error GoTo 0
End Function

Sub test()
 Dim flDlg As FileDialog
 Dim dblCount As Double
 
 Set flDlg = Application.FileDialog(msoFileDialogFolderPicker)
 flDlg.Show
 dblCount = CountFiles(flDlg.SelectedItems(1))
 Debug.Print dblCount
End Sub

Function GetFileCount(ByVal Folder As Variant, Optional ByVal FileFilter As String) As Variant
 Dim Files As Object
 
 If FileFilter = "" Then FileFilter = "*.*"
 
   With CreateObject("Shell.Application")
     Set Files = .Namespace(Folder).Items
     Files.Filter 64, FileFilter
     GetFileCount = Files.Count
   End With
End Function

Sub FileCountTest()
 Dim FileCount As Long
 Dim Folder As String
 
 With Application.FileDialog(msoFileDialogFolderPicker)
   If .Show = -1 Then
     Folder = .SelectedItems(1)
   Else
     Exit Sub
   End If
 End With
 
 FileCount = GetFileCount(Folder, "*.xls*")
 Debug.Print FileCount
End Sub

Sub testSimpler()
 Dim fld As String
 Dim lst As Variant
 
 fld = "C:\test\*.xl*"
 lst = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir" & fld & " /b /a-d").stdout.readall, vbCrLf), ".")
 MsgBox UBound(lst) + 1
End Sub

fld = Chr(34) & ThisWorkbook.Path & "\Test Folder\*.xl*" & Chr(34)