excel - VBA excel计算文件夹(和文件)总数

Question

我有以下脚本。想要文件夹、子文件夹和文件的数量:

Sub CountFiles(ByVal path1 As String)

Dim fso As Object
Dim subfolder As Object
Dim file As Object
Dim folder As Object
Dim stetje As Long

Set fso = CreateObject("Scripting.FileSystemObject")

Set folder = fso.GetFolder(path1)

For Each subfolder In folder.SubFolders
 CountFiles (subfolder.path)
 
Next subfolder

For Each file In folder.Files


Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = file.path



Next file


Set fso = Nothing
Set folder = Nothing
Set subfolder = Nothing
Set file = Nothing

End Sub

你称之为:

Sub someStuff()
Call CountFiles ("c:/temp/test/")
End Sub

此脚本将所有文件夹、子文件夹和文件的路径写入 Excel 单元格
但我真正想要的是将所有出现的总数计入一个变量。
所以代替这个:

 For Each file In folder.Files
    
    
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = file.path
    
    
 Next file

我想要这样的东西:

 For Each file In folder.Files
    
    number = number +  file.path.Count // of course this line is completely pseudo

 Next file

因此，想要的输出例如是数字:2345 而不是 2345 行，其中路径被写出。
任何帮助/提示将不胜感激!

Answer 1

这是一种方法:

Function CountFiles(ByVal path As String) As Long

    Dim fso As Object
    Dim folder As Object
    Dim subfolder As Object
    Dim amount As Long
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Set folder = fso.GetFolder(path)
    For Each subfolder In folder.SubFolders
        amount = amount + CountFiles(subfolder.path)
    Next subfolder
    
    amount = amount + folder.Files.Count
    
    Set fso = Nothing
    Set folder = Nothing
    Set subfolder = Nothing
    
    CountFiles = amount

End Function

Sub someStuff()
    MsgBox CountFiles("c:/temp/test/")
End Sub

我已经将 sub 变成了一个函数，它返回在该文件夹和子文件夹中找到的文件数量。和以前一样，这是递归的。