使用Dir，遍历文件夹下所有子文件夹及文件

'-------------------------------------------
'获取某文件夹下所有文件和子目录下的文件
'-------------------------------------------
Sub getAllFile()

Cells.ClearContents

Call getFileNm(ChooseFolder(), 0, 0)

MsgBox "处理完成！"

End Sub

'-------------------------------------------
'获取目标文夹路径
'-------------------------------------------
Public Function ChooseFolder() As String
    Dim dlgOpen As FileDialog
    Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)
    With dlgOpen
        If .Show = -1 Then
                ChooseFolder = .SelectedItems(1)
        End If
    End With
    Set dlgOpen = Nothing
End Function

'-------------------------------------------
'获取文件夹下所有文件和文件夹名称
'row worksheet里的打印行
'col worksheet里的打印列（根据层次显示）
'-------------------------------------------
Public Sub getFileNm(ByVal subFolderPath As String, _
                            ByRef row As Integer, _
                            ByVal col As Integer)
    Dim fileName As String
    Dim subFolderVisited As String
    
    col = col + 1
    subFolderPath = subFolderPath & ""
    fileName = Dir(subFolderPath, vbDirectory)
    Do While fileName <> ""
        If fileName <> "." And fileName <> ".." Then
            If GetAttr(subFolderPath & fileName) And vbDirectory Then
                '文件夹的场合
                row = row + 1
                Worksheets(1).Cells(row, col) = fileName
                Call getFileNm(subFolderPath & fileName, row, col)
            Else
                '文件的场合
                row = row + 1
                Worksheets(1).Cells(row, col) = fileName
            End If
        End If
        '获取执行递归前的下一个文件
        subFolderVisited = Dir(subFolderPath, vbDirectory)
        Do While subFolderVisited <> fileName
            subFolderVisited = Dir
        Loop
        fileName = Dir
    Loop

End Sub