时间戳

首页 > 解决方案 > VBA excel计算文件夹（和文件）总数

excel - VBA excel计算文件夹（和文件）总数

问题描述

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

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 行，其中路径被写出。

任何帮助/提示将不胜感激！

标签： excelvbaforeachvba7

解决方案

这是一种方法：

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 变成了一个函数，它返回在该文件夹和子文件夹中找到的文件数量。和以前一样，这是递归的。

推荐阅读