获取 VBA 中的子目录列表

Get list of sub-directories in VBA

提问by Matthias Pospiech

• I want to get a list of all sub-directories within a directory.
• If that works I want to expand it to a recursive function.

• 我想获取目录中所有子目录的列表。
• 如果可行，我想将其扩展为递归函数。

However my initial approach to get the subdirs fails. It simply shows everything including files:

但是，我最初获取子目录的方法失败了。它只是显示包括文件在内的所有内容：

sDir = Dir(sPath, vbDirectory)
Do Until LenB(sDir) = 0
    Debug.Print sDir
    sDir = Dir
Loop

The list starts with '..' and several folders and ends with '.txt' files.

该列表以“..”和几个文件夹开头，以“.txt”文件结尾。

---

---

EDIT:
I should add that this must run in Word, not Excel (many functions are not available in Word) and it is Office 2010.

编辑：
我应该补充一点，这必须在 Word 中运行，而不是 Excel（许多功能在 Word 中不可用），它是 Office 2010。

---

---

EDIT 2:

编辑2：

One can determine the type of the result using

可以使用确定结果的类型

iAtt = GetAttr(sPath & sDir)
If CBool(iAtt And vbDirectory) Then
   ...
End If 

But that gave me new problems, so that I am now using a code based on Scripting.FileSystemObject.

但这给我带来了新的问题，所以我现在使用的是基于Scripting.FileSystemObject.

回答by brettdj

Updated July 2014: Added PowerShelloption and cut back the second code to list folders only

2014 年 7 月更新：添加PowerShell选项并减少第二个代码以仅列出文件夹

The methods below that run a full recursive process in place of FileSearchwhich was deprecated in Office 2007. (The later two codes use Excel for output only - this output can be removed for running in Word)

下面运行完整递归过程的方法在FileSearchOffice 2007 中已弃用。（后两个代码仅使用 Excel 进行输出 - 可以删除此输出以在 Word 中运行）

1. Shell PowerShell
2. Using FSOwith Dirfor filtering file type. Sourced from this EE answerwhich sits behind the EE paywall. This is longer than what you asked for (a list of folders) but i think it is useful as it gives you an array of results to work further with
3. Using Dir. This example comes from my answer I supplied on another site

1. 贝壳 PowerShell
2. 使用FSO与Dir用于过滤的文件类型。来自这个位于 EE 付费墙后面的 EE答案。这比您要求的（文件夹列表）要长，但我认为它很有用，因为它为您提供了一系列结果以供进一步处理
3. 使用Dir. 这个例子来自我在另一个网站上提供的答案

1. Using PowerShellto dump all folders below C:\temp into a csv file

1.PowerShell用于将 C:\temp 下的所有文件夹转储到一个 csv 文件中

Sub Comesfast()
X2 = Shell("powershell.exe Get-ChildItem c:\temp -Recurse | ?{ $_.PSIsContainer } | export-csv C:\temp\filename.csv", 1)
End Sub

2. Using FileScriptingObjectto dump all folders below C:\temp into Excel

2.使用FileScriptingObject将C:\temp下的所有文件夹转储到Excel中

Public Arr() As String
Public Counter As Long

Sub LoopThroughFilePaths()
Dim myArr
Dim strPath As String
strPath = "c:\temp\"
myArr = GetSubFolders(strPath)
[A1].Resize(UBound(myArr, 1), 1) = Application.Transpose(myArr)
End Sub


Function GetSubFolders(RootPath As String)
Dim fso As Object
Dim fld As Object
Dim sf As Object
Dim myArr

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(RootPath)
For Each sf In fld.SUBFOLDERS
    ReDim Preserve Arr(Counter)
    Arr(Counter) = sf.Path
    Counter = Counter + 1
    myArr = GetSubFolders(sf.Path)
Next
GetSubFolders = Arr
Set sf = Nothing
Set fld = Nothing
Set fso = Nothing
End Function

3 Using Dir

3 使用 Dir

    Option Explicit

    Public StrArray()
    Public lngCnt As Long
    Public b_OS_XP As Boolean

    Public Enum MP3Tags
    '  See http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=160880&page=1 for OS specific attribute lists
    XP_Artist = 16
    XP_AlbumTitle = 17
    XP_SongTitle = 10
    XP_TrackNumber = 19
    XP_RecordingYear = 18
    XP_Genre = 20
    XP_Duration = 21
    XP_BitRate = 22
    Vista_W7_Artist = 13
    Vista_W7_AlbumTitle = 14
    Vista_W7_SongTitle = 21
    Vista_W7_TrackNumber = 26
    Vista_W7_RecordingYear = 15
    Vista_W7_Genre = 16
    Vista_W7_Duration = 17
    Vista_W7_BitRate = 28
    End Enum

    Public Sub Main()
    Dim objws
    Dim objWMIService
    Dim colOperatingSystems
    Dim objOperatingSystem
    Dim objFSO
    Dim objFolder
    Dim Wb As Workbook
    Dim ws As Worksheet
    Dim strobjFolderPath As String
    Dim strOS As String
    Dim strMyDoc As String
    Dim strComputer As String

   'Setup Application for the user
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With    

    'reset public variables
    lngCnt = 0
    ReDim StrArray(1 To 10, 1 To 1000)

    ' Use wscript to automatically locate the My Documents directory
    Set objws = CreateObject("wscript.shell")
    strMyDoc = objws.SpecialFolders("MyDocuments")


    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\" & strComputer & "\root\cimv2")
    Set colOperatingSystems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")
    For Each objOperatingSystem In colOperatingSystems
        strOS = objOperatingSystem.Caption
    Next

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    If InStr(strOS, "XP") Then
        b_OS_XP = True
    Else
        b_OS_XP = False
    End If


    ' Format output sheet
    Set Wb = Workbooks.Add(1)
    Set ws = Wb.Worksheets(1)
    ws.[a1] = Now()
    ws.[a2] = strOS
    ws.[a3] = strMyDoc
    ws.[a1:a3].HorizontalAlignment = xlLeft

    ws.[A4:J4].Value = Array("Folder", "File", "Artist", "Album Title", "Song Title", "Track Number", "Recording Year", "Genre", "Duration", "Bit Rate")
    ws.Range([a1], [j4]).Font.Bold = True
    ws.Rows(5).Select
    ActiveWindow.FreezePanes = True


    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(strMyDoc)

    ' Start the code to gather the files
    ShowSubFolders objFolder, True
    ShowSubFolders objFolder, False

    If lngCnt > 0 Then
        ' Finalise output
        With ws.Range(ws.[a5], ws.Cells(5 + lngCnt - 1, 10))
            .Value2 = Application.Transpose(StrArray)
            .Offset(-1, 0).Resize(Rows.Count - 3, 10).AutoFilter
            .Offset(-4, 0).Resize(Rows.Count, 10).Columns.AutoFit
        End With
        ws.[a1].Activate
    Else
        MsgBox "No files found!", vbCritical
        Wb.Close False
    End If

    ' tidy up

    Set objFSO = Nothing
    Set objws = Nothing

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .StatusBar = vbNullString
    End With
    End Sub

    Sub ShowSubFolders(ByVal objFolder, bRootFolder As Boolean)
    Dim objShell
    Dim objShellFolder
    Dim objShellFolderItem
    Dim colFolders
    Dim objSubfolder


    'strName must be a variant, as ParseName does not work with a string argument
    Dim strFname
    Set objShell = CreateObject("Shell.Application")
    Set colFolders = objFolder.SubFolders
    Application.StatusBar = "Processing " & objFolder.Path

    If bRootFolder Then
        Set objSubfolder = objFolder
        GoTo OneTimeRoot
    End If

    For Each objSubfolder In colFolders
        'check to see if root directory files are to be processed
    OneTimeRoot:
        strFname = Dir(objSubfolder.Path & "\*.mp3")
        Set objShellFolder = objShell.Namespace(objSubfolder.Path)
        Do While Len(strFname) > 0
            lngCnt = lngCnt + 1
            If lngCnt Mod 1000 = 0 Then ReDim Preserve StrArray(1 To 10, 1 To (lngCnt + 1000))
            Set objShellFolderItem = objShellFolder.ParseName(strFname)
            StrArray(1, lngCnt) = objSubfolder
            StrArray(2, lngCnt) = strFname
            If b_OS_XP Then
                StrArray(3, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.XP_Artist)
                StrArray(4, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.XP_AlbumTitle)
                StrArray(5, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.XP_SongTitle)
                StrArray(6, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.XP_TrackNumber)
                StrArray(7, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.XP_RecordingYear)
                StrArray(8, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.XP_Genre)
                StrArray(9, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.XP_Duration)
                StrArray(10, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.XP_BitRate)
            Else
                StrArray(3, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.Vista_W7_Artist)
                StrArray(4, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.Vista_W7_AlbumTitle)
                StrArray(5, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.Vista_W7_SongTitle)
                StrArray(6, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.Vista_W7_TrackNumber)
                StrArray(7, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.Vista_W7_RecordingYear)
                StrArray(8, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.Vista_W7_Genre)
                StrArray(9, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.Vista_W7_Duration)
                StrArray(10, lngCnt) = objShellFolder.GetDetailsOf(objShellFolderItem, MP3Tags.Vista_W7_BitRate)
            End If
            strFname = Dir
        Loop
        If bRootFolder Then
            bRootFolder = False
            Exit Sub
        End If
        ShowSubFolders objSubfolder, False
    Next
    End Sub

回答by Fionnuala

You would be better off with the FileSystemObject. I reckon.

使用 FileSystemObject 会更好。我认为。

To call this you just need, say: listfolders "c:\data"

要调用它，您只需要说： listfolders "c:\data"

Sub listfolders(startfolder)
''Reference Windows Script Host Object Model
''If you prefer, just Dim everything as Object
''and use CreateObject("Scripting.FileSystemObject")
Dim fs As New FileSystemObject
Dim fl1 As Folder
Dim fl2 As Folder

Set fl1 = fs.GetFolder(startfolder)

For Each fl2 In fl1.SubFolders
    Debug.Print fl2.Path
    listfolders fl2.Path
Next

End Sub

回答by cheezsteak

Here is a Simple version without using Scripting.FileSystemObjectbecause I found it slow and unreliable. In particular the .Namemethod, was slowing everything down. Also I tested this in Excel but I don't think anything I used wouldn't be available in Word.

这是一个没有使用的简单版本，Scripting.FileSystemObject因为我发现它很慢而且不可靠。特别是.Name方法，正在减慢一切。我也在 E​​xcel 中对此进行了测试，但我认为我使用的任何内容都不会在 Word 中可用。

First some functions:

首先是一些功能：

This joins two strings to create a file path, similar to os.path.joinin python. It is useful for not needing to remember if you tacked on that "\" at the end of your path.

这将连接两个字符串以创建文件路径，类似于os.path.join在 python 中。这对于不需要记住是否在路径末尾添加了“\”很有用。

Const sep as String = "\"

Function pjoin(root_path As String, file_path As String) As String
    If right(root_path, 1) = sep Then
        pjoin = root_path & file_path
    Else
        pjoin = root_path & sep & file_path
    End If
End Function

This create a collection of sub items of root directory root_path

这将创建根目录的子项集合 root_path

Function subItems(root_path As String, Optional pat As String = "*", _
                  Optional vbtype As Integer = vbNormal) As Collection
    Set subItems = New Collection
    Dim sub_item As String
    sub_item= Dir(pjoin(root_path, pat), vbtype)
    While sub_item <> ""
        subItems.Add (pjoin(root_path, sub_item))
        sub_item = Dir()
    Wend
End Function

This creates a collection of sub items in directory root_paththat including folders and then removes items that are not folders from the collection. And it can optionally remove those nasty .and ..folders

这会在root_path包含文件夹的目录中创建子项目的集合，然后从集合中删除不是文件夹的项目。它可以有选择地删除那些讨厌.和..文件夹

Function subFolders(root_path As String, Optional pat As String = "", _
                    Optional skipDots As Boolean = True) As Collection
    Set subFolders = subItems(root_path, pat, vbDirectory)
    If skipDots Then
        Dim dot As String
        Dim dotdot As String
        dot = pjoin(root_path, ".")
        dotdot = dot & "."
        Do While subFolders.Item(1) = dot _
        Or subFolders.Item(1) = dotdot
            subFolders.remove (1)
            If subFolders.Count = 0 Then Exit Do
        Loop
    End If
    For i = subFolders.Count To 1 Step -1
        ' This comparison could be replaced by and `fileExists` function
        If Dir(subFolders.Item(i), vbNormal) <> "" Then
            subFolders.remove (i)
        End If
    Next i
End Function

Finally is the recursive search function based on someone else function from this site that used Scripting.FileSystemObjectI haven't done any comparison tests between it and the original. If I find that post again I will link it. Note collecis passed by reference so create a new collection and call this sub to populate it. Pass vbType:=vbDirectoryfor all sub folders.

最后是基于该站点其他函数的递归搜索函数，Scripting.FileSystemObject我没有对它和原始函数进行任何比较测试。如果我再次找到那个帖子，我会链接它。Notecollec是通过引用传递的，因此创建一个新集合并调用此子集来填充它。通过vbType:=vbDirectory所有子文件夹。

Sub walk(root_path As String, ByRef collec as Collection, Optional pat As String = "*" _
         Optional vbType as Integer = vbNormal)
    Dim subF as Collection
    Dim subD as Collection
    Set subF = subItems(root_path, pat, vbType)
    For Each sub_file In subF
        collec.Add sub_file 
    Next sub_file 
    Set subD = subFolders(root_path)
    For Each sub_folder In subD
        walk sub_folder , collec, pat, vbType
    Next sub_folder 
End Sub

回答by stenci

Here is a VBA solution, without using external objects.

这是一个 VBA 解决方案，不使用外部对象。

Because of the limitations of the Dir()function you need to get the whole content of each folder at once, not while crawling with a recursive algorithm.

由于Dir()函数的限制，您需要一次获取每个文件夹的全部内容，而不是同时使用递归算法进行爬行。

Function GetFilesIn(Folder As String) As Collection
  Dim F As String
  Set GetFilesIn = New Collection
  F = Dir(Folder & "\*")
  Do While F <> ""
    GetFilesIn.Add F
    F = Dir
  Loop
End Function

Function GetFoldersIn(Folder As String) As Collection
  Dim F As String
  Set GetFoldersIn = New Collection
  F = Dir(Folder & "\*", vbDirectory)
  Do While F <> ""
    If GetAttr(Folder & "\" & F) And vbDirectory Then GetFoldersIn.Add F
    F = Dir
  Loop
End Function

Sub Test()
  Dim C As Collection, F

  Debug.Print
  Debug.Print "Files in C:\"
  Set C = GetFilesIn("C:\")
  For Each F In C
    Debug.Print F
  Next F

  Debug.Print
  Debug.Print "Folders in C:\"
  Set C = GetFoldersIn("C:\")
  For Each F In C
    Debug.Print F
  Next F
End Sub

EDIT

编辑

This version digs into subfolders and returns full path names instead of returning just the file or folder name.

此版本深入子文件夹并返回完整路径名，而不是仅返回文件或文件夹名称。

Do NOT run the test with on the whole C drive!!

不要在整个 C 驱动器上运行测试！！

Function GetFilesIn(Folder As String, Optional Recursive As Boolean = False) As Collection
  Dim F As String
  Set GetFilesIn = New Collection
  F = Dir(Folder & "\*")
  Do While F <> ""
    GetFilesIn.Add JoinPaths(Folder, F)
    F = Dir
  Loop

  If Recursive Then
    Dim SubFolder, SubFile
    For Each SubFolder In GetFoldersIn(Folder)
      If Right(SubFolder, 2) <> "\." And Right(SubFolder, 3) <> "\.." Then
        For Each SubFile In GetFilesIn(CStr(SubFolder), True)
          GetFilesIn.Add SubFile
        Next SubFile
      End If
    Next SubFolder
  End If
End Function

Function GetFoldersIn(Folder As String) As Collection
  Dim F As String
  Set GetFoldersIn = New Collection
  F = Dir(Folder & "\*", vbDirectory)
  Do While F <> ""
    If GetAttr(Folder & "\" & F) And vbDirectory Then GetFoldersIn.Add JoinPaths(Folder, F)
    F = Dir
  Loop
End Function

Function JoinPaths(Path1 As String, Path2 As String) As String
  JoinPaths = Replace(Path1 & "\" & Path2, "\", "\")
End Function

Sub Test()
  Dim C As Collection, F

  Debug.Print
  Debug.Print "Files in C:\"
  Set C = GetFilesIn("C:\")
  For Each F In C
    Debug.Print F
  Next F

  Debug.Print
  Debug.Print "Folders in C:\"
  Set C = GetFoldersIn("C:\")
  For Each F In C
    Debug.Print F
  Next F

  Debug.Print
  Debug.Print "All files in C:\"
  Set C = GetFilesIn("C:\", True)
  For Each F In C
    Debug.Print F
  Next F
End Sub