'-------------------------------------------

'获取某文件夹下所有文件和子目录下的文件

'-------------------------------------------

Sub getAllFile()

Cells.ClearContents

Call getFileNm(ChooseFolder(), , )

MsgBox "处理完成！"

End Sub

'-------------------------------------------

'获取目标文夹路径

'-------------------------------------------

Public Function ChooseFolder() As String

    Dim dlgOpen As FileDialog

    Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)

    With dlgOpen

        If .Show = - Then

                ChooseFolder = .SelectedItems()

        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 +

    subFolderPath = subFolderPath & "\"

    fileName = Dir(subFolderPath, vbDirectory)

    Do While fileName <> ""

        If fileName <> "." And fileName <> ".." Then

            If GetAttr(subFolderPath & fileName) And vbDirectory Then
                '文件夹的场合

                row = row +

                Worksheets().Cells(row, col) = fileName

                Call getFileNm(subFolderPath & fileName, row, col)

            Else

                '文件的场合

                row = row +

                Worksheets().Cells(row, col) = fileName

            End If

        End If

        '获取执行递归前的下一个文件

        subFolderVisited = Dir(subFolderPath, vbDirectory)

        Do While subFolderVisited <> fileName

            subFolderVisited = Dir

        Loop

        fileName = Dir

    Loop

End Sub