问是否可以列出自定义目录中的所有文件和文件夹- excel vba

EN

Option Explicit

Private Sub test()
    readFileSystem ("C:\Temp\")
End Sub

Private Sub readFileSystem(ByVal pFolder As String)
    Dim oFSO As Object
    Dim oFolder As Object

    ' create FSO
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    ' get start folder
    Set oFolder = oFSO.getFolder(pFolder)

    ' list folder content
    listFolderContent oFolder

    ' destroy FSO
    Set oFolder = Nothing
    Set oFSO = Nothing
End Sub

Private Sub listFolderContent(ByVal pFolder As Object)
    Dim oFile As Object
    Dim oFolder As Object

    ' go thru all sub folders
    For Each oFolder In pFolder.SubFolders
        Debug.Print oFolder.Path
        ' do the recursion to list sub folder content
        listFolderContent oFolder
    Next

    ' list all files in that directory
    For Each oFile In pFolder.Files
        Debug.Print oFile.Path
    Next

    ' destroy all objects
    Set pFolder = Nothing
    Set oFile = Nothing
    Set oFolder = Nothing
End Sub

Sub MM()

Dim fileResults As Variant

fileResults = GetFiles("C:\Users\Macro Man\Documents")

Range("A1").Resize(UBound(fileResults) + 1, 1).Value = _
    WorksheetFunction.Transpose(fileResults)

End Sub


'// UDF to populate array with files, assign to a Variant variable. 
Function GetFiles(parentFolder As String) As Variant

GetFiles = Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR """ & parentFolder & _
    IIf(Right(parentFolder, 1) = "\", vbNullString, "\") & "*.*"" /S /B /A:-D").StdOut.ReadAll, vbCrLf), ".")

End Function

Option Explicit

Sub Test()

    Dim strFolder As String
    Dim objFolders As Object
    Dim objFiles As Object
    Dim i As Long
    Dim objItem As Object

    ' target folder
    strFolder = "C:\Test"

    ' loop through all folders and files
    Set objFolders = CreateObject("Scripting.Dictionary")
    Set objFiles = CreateObject("Scripting.Dictionary")
    objFolders(0) = strFolder
    i = 0
    With CreateObject("Scripting.FileSystemObject")
        Do
            With .GetFolder(objFolders(i))
                For Each objItem In .Files
                    objFiles(objFiles.Count) = objItem.Path
                Next
                For Each objItem In .SubFolders
                    objFolders(objFolders.Count) = objItem.Path
                Next
            End With
            i = i + 1
        Loop Until i = objFolders.Count
    End With

    ' results output to the 1st sheet
    With Sheets(1)
        .Select
        .Cells.Delete
        .Range(.Cells(1, 1), .Cells(objFolders.Count, 1)).Value = Application.Transpose(objFolders.Items)
        .Range(.Cells(1, 2), .Cells(objFiles.Count, 2)).Value = Application.Transpose(objFiles.Items)
        .Columns.AutoFit
    End With

End Sub