1. 页面布局

在“main”Sheet中按照下面的格式编辑。

2. 实现代码

Private wsMain As Worksheet
Private intIdx As Long

Private Sub getExcelBookList(strPath As String)
    Dim fso As Object
    Dim objFile As Object
    Dim objFolder As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    For Each objFolder In fso.GetFolder(strPath).SubFolders
        Call getExcelBookList(objFolder.Path)
    Next objFolder

    For Each objFile In fso.GetFolder(strPath).Files
        If Left(objFile.Name, 1) <> "~" Then
            wsMain.Cells(intIdx, 3) = strPath
            wsMain.Cells(intIdx, 4) = objFile.Name
            intIdx = intIdx + 1
        End If
    Next objFile
    
    Set objFile = Nothing
    Set objFolder = Nothing
    Set fso = Nothing
End S