VBA将所有子文件夹写入工作表

Sub WriteSubfoldersToWorksheet()
    Dim folderPath As String
    Dim currentRow As Long
    
    ' 设置文件夹路径
    folderPath = "C:\YourFolderPath"
    
    ' 设置工作表
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' 初始化当前行
    currentRow = 1
    
    ' 调用递归函数遍历文件夹
    ListSubfolders folderPath, currentRow, ws
End Sub

Sub ListSubfolders(folderPath As String, ByRef currentRow As Long, ws As Worksheet)
    Dim folder As Object
    Dim subfolder As Object
    
    ' 创建文件系统对象
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' 获取文件夹对象
    Set folder = fso.GetFolder(folderPath)
    
    ' 遍历子文件夹
    For Each subfolder In folder.Subfolders
        ' 将子文件夹路径写入工作表
        ws.Cells(currentRow, 1).Value = subfolder.Path
        currentRow = currentRow + 1
        
        ' 递归调用遍历子文件夹的子文件夹
        ListSubfolders subfolder.Path, currentRow, ws
    Next subfolder
    
    ' 释放对象
    Set fso = Nothing
    Set folder = Nothing
    Set subfolder = Nothing
End Sub