访问VBA搜索文件夹和子文件夹并将结果追加到表中

Sub SearchFolders()
    Dim FolderPath As String
    Dim FileName As String
    Dim ws As Worksheet
    Dim i As Integer
    
    ' 设置文件夹路径
    FolderPath = "C:\YourFolderPath"
    
    ' 设置文件名
    FileName = "YourFileName"
    
    ' 设置工作表
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 清空表格中的内容
    ws.Cells.ClearContents
    
    ' 设置表头
    ws.Cells(1, 1).Value = "文件路径"
    ws.Cells(1, 2).Value = "文件名"
    
    ' 初始化行数
    i = 2
    
    ' 调用搜索文件夹的函数
    SearchFiles FolderPath, FileName, ws, i
End Sub

Sub SearchFiles(ByVal FolderPath As String, ByVal FileName As String, ByRef ws As Worksheet, ByRef i As Integer)
    Dim SubFolder As Object
    Dim File As Object
    Dim SubFolderItems As Object
    Dim FileItems As Object
    
    ' 获取文件夹对象
    Set SubFolderItems = CreateObject("Scripting.FileSystemObject").GetFolder(FolderPath).SubFolders
    
    ' 遍历子文件夹
    For Each SubFolder In SubFolderItems
        ' 调用搜索文件夹的函数（递归）
        SearchFiles SubFolder.Path, FileName, ws, i
    Next SubFolder
    
    ' 获取文件对象
    Set FileItems = CreateObject("Scripting.FileSystemObject").GetFolder(FolderPath).Files
    
    ' 遍历文件
    For Each File In FileItems
        ' 判断文件名是否匹配
        If InStr(1, File.Name, FileName, vbTextCompare) > 0 Then
            ' 将结果追加到表格中
            ws.Cells(i, 1).Value = File.Path
            ws.Cells(i, 2).Value = File.Name
            i = i + 1
        End If
    Next File
End Sub