Excel VBA扫描目录内容和更新表

Sub ScanDirectory()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim path As String
    Dim row As Integer
    
    ' 设置目录路径
    path = "C:\目录路径"
    
    ' 创建FileSystemObject对象
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' 获取目录对象
    Set folder = fso.GetFolder(path)
    
    ' 初始化行数
    row = 1
    
    ' 遍历目录下的文件和文件夹
    For Each file In folder.Files
        ' 将文件名和路径写入Excel表格
        Cells(row, 1).Value = file.Name
        Cells(row, 2).Value = file.Path
        row = row + 1
    Next file
    
    ' 释放对象
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing
End Sub

Sub UpdateTable()
    Dim ws As Worksheet
    Dim row As Integer
    
    ' 设置工作表
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' 初始化行数
    row = 2
    
    ' 清空表格数据
    ws.Range("A2:B" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row).ClearContents
    
    ' 扫描目录并更新表格
    ScanDirectory
    
    ' 将扫描结果更新到表格中
    For Each cell In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
        cell.Offset(0, 1).Value = cell.Offset(0, 1).Value & "\" & cell.Value
    Next cell
End Sub