是否可以列出自定义目录中的所有文件和文件夹 - excel vba [英] Is it possible to list all the files and folders in a custom directory - excel vba

问题描述

我想知道在 excel VBA 中是否有任何或所有这些功能:

I wanted to know whether any or all of these functions are possible in excel VBA or not:

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

• List all the folders and sub folders within a local area (path name).

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

Produce a link so when displayed the user can open it from the spreadsheet.

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

Automatically update on the spreadsheet if user adds or deletes any files or folder/subfolder from a directory.

推荐答案

我做了一个简单的例子来告诉你如何列出所有文件和子文件夹:

I did a quick example to show you how to list all files and sub folders:

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

这篇关于是否可以列出自定义目录中的所有文件和文件夹 - excel vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！