excel - 如何使用vba获取目录中的文件夹和子文件夹中有多少文件

Question

我想知道文件夹及其子文件夹中有多少文件。我在这里搜索和一些谷歌页面，但我没有找到我要找的东西。我一直在寻找的最接近的是这个。

    Sub MainList()
    'Updateby Extendoffice
    Set folder = Application.FileDialog(msoFileDialogFolderPicker)
    If folder.Show <> -1 Then Exit Sub
    xDir = folder.SelectedItems(1)
    Call ListFilesInFolder(xDir, True)
    End Sub

    Sub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
    Dim xFileSystemObject As Object
    Dim xFolder As Object
    Dim xSubFolder As Object
    Dim xFile As Object
    Dim rowIndex As Long
    Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFileSystemObject.GetFolder(xFolderName)
    rowIndex = Application.ActiveSheet.Range("A65536").End(xlUp).Row + 1
    For Each xFile In xFolder.Files
      Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
      rowIndex = rowIndex + 1
    Next xFile
    If xIsSubfolders Then
      For Each xSubFolder In xFolder.SubFolders
        ListFilesInFolder xSubFolder.Path, True
      Next xSubFolder
    End If
    Set xFile = Nothing
    Set xFolder = Nothing
    Set xFileSystemObject = Nothing
    End Sub

    Function GetFileOwner(ByVal xPath As String, ByVal xName As String)
    Dim xFolder As Object
    Dim xFolderItem As Object
    Dim xShell As Object
    xName = StrConv(xName, vbUnicode)
    xPath = StrConv(xPath, vbUnicode)
    Set xShell = CreateObject("Shell.Application")
    Set xFolder = xShell.Namespace(StrConv(xPath, vbFromUnicode))
    If Not xFolder Is Nothing Then
      Set xFolderItem = xFolder.ParseName(StrConv(xName, vbFromUnicode))
    End If
    If Not xFolderItem Is Nothing Then
      GetFileOwner = xFolder.GetDetailsOf(xFolderItem, 8)
    Else
      GetFileOwner = ""
    End If
    Set xShell = Nothing
    Set xFolder = Nothing
    Set xFolderItem = Nothing
    End Function

但它给了我文件列表。我想知道每个文件夹有多少个文件？你有什么建议吗？

编辑：

-MainFolder
  -- SubFolder1 -- Has .. file
  -- SubFolder2 -- Has .. file
     --SubFolder2 subfolder -- Has .. file
     --SubFolder2 subfolder1 -- Has .. file
     --SubFolder2 subfolder2 -- Has .. file
  -- SubFolder3 -- Has .. file
.
.
.

我想在 excel 工作表中使用这种结构。

标签： excelvba