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

Question

我想知道这些功能中的任何一个或全部是否可以在 Excel VBA 中实现:

• 列出本地区域内的所有文件夹和子文件夹(路径名)。

• 生成一个链接，以便在显示时用户可以从电子表格中打开它。

• 如果用户在目录中添加或删除任何文件或文件夹/子文件夹，则会自动更新电子表格。

Answer 1

我做了一个简单的示例来向您展示如何列出所有文件和子文件夹:

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