VBA 遍历目录

缩进等格式设置可以通过录制宏解决。

Sub GetFolderFile(path As String, rowNo As Long, indentLevel As Integer)

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim curFolder As Object
    Set curFolder = fso.GetFolder(path)
    Dim subFolders As Variant
    Set subFolders = curFolder.subFolders
    Dim files As Variant
    Set files = curFolder.files
    
    With Sheet2
    
        ' 画面をスクロールする
        If rowNo > SCROLL_THRESHOLD Then
            ActiveWindow.ScrollRow = rowNo - SCROLL_THRESHOLD
        End If
            
        If rowNo <> S2_FIRST_ROW Then
            ' フォルダ記載行の作成
            Dim lastRow As Integer
            lastRow = rowNo - 1
            .Rows(lastRow).Copy .Rows(rowNo)
        End If
        .Cells(rowNo, S2_COL).Value = FOLDER_SIGN & curFolder.name
        
        ' インデントの指定
        With .Cells(rowNo, S2_COL)
            .AddIndent = False
            .indentLevel = indentLevel
        End With
        
        rowNo = rowNo + 1
        
        Dim file As Object
        For Each file In files
        
            ' 画面をスクロールする
            If rowNo > SCROLL_THRESHOLD Then
                ActiveWindow.ScrollRow = rowNo - SCROLL_THRESHOLD
            End If
            
            ' ファイル記載行の作成
            lastRow = rowNo - 1
            .Rows(lastRow).Copy .Rows(rowNo)
            .Cells(rowNo, S2_COL).Value = file.name
            
            ' インデントの指定
            With .Cells(rowNo, S2_COL)
                .AddIndent = False
                .indentLevel = indentLevel + 1
            End With
            
            rowNo = rowNo + 1
            
        Next
        
    End With
    
    ' 次のサブフォルダを再帰処理
    Dim subFolder As Object
    For Each subFolder In subFolders
        Call GetFolderFile(subFolder.path, rowNo, indentLevel + 1)
    Next
End Sub