一、建立工程
首先，我們需要建立一個新的VBA項目，打開Visual Basic Editor，按下ALT+F11，在彈出的窗口中選擇“插入”－“模塊”，即可新建一個空的模塊。
Sub TraverseFolders()
End Sub二、定義變量
在Sub過程中，我們可以定義多個變量，變量名可以自由命名，但類型需要定義為“String”或“Variant”，這里定義了一個主文件夾路徑MainFolder，以及ListObject對象tbl用于存儲所有文件路徑。
Sub TraverseFolders()
Dim MainFolder As String
Dim tbl As ListObject
End Sub三、定義ListObject表格
ListObject表格是一種強大的表格功能，擁有各種排序、篩選、格式控制功能等等。這里我們定義一個新的表格，命名為“FileList”，將表頭設置為“File Name”和“Path”，并將其填充至工作表的A1單元格。
Sub TraverseFolders()
Dim MainFolder As String
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:B1"), , xlYes)
tbl.Name = "FileList"
tbl.HeaderRowRange.Cells(1, 1).Value = "File Name"
tbl.HeaderRowRange.Cells(1, 2).Value = "Path"
End Sub四、定義遞歸過程
遞歸是一種經典的程序設計思路，用于處理一些可分解為多個子問題的問題，比如樹形數據結構、文件夾中的文件等等。這里我們定義一個子過程TraverseSubfolders，它接受兩個參數，FolderPath表示當前文件夾路徑，tbl表示傳入的ListObject表格。
Sub TraverseFolders()
Dim MainFolder As String
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:B1"), , xlYes)
tbl.Name = "FileList"
tbl.HeaderRowRange.Cells(1, 1).Value = "File Name"
tbl.HeaderRowRange.Cells(1, 2).Value = "Path"
TraverseSubfolders MainFolder, tbl
End Sub
Sub TraverseSubfolders(FolderPath As String, tbl As ListObject)
End Sub五、遍歷文件夾并加載子文件夾
在TraverseSubfolders過程中，我們需要使用VBA內置函數FileSystemObject（FSO）來處理文件夾和文件。首先，我們需要在過程開始時判斷指定路徑是否為文件夾，如果是，則遍歷文件夾內所有文件并添加到ListObject表格中，否則遞歸處理子文件夾。注意，在遞歸過程中需要先處理所有的子文件夾才能處理當前文件夾。
Sub TraverseFolders()
Dim MainFolder As String
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:B1"), , xlYes)
tbl.Name = "FileList"
tbl.HeaderRowRange.Cells(1, 1).Value = "File Name"
tbl.HeaderRowRange.Cells(1, 2).Value = "Path"
TraverseSubfolders MainFolder, tbl
End Sub
Sub TraverseSubfolders(FolderPath As String, tbl As ListObject)
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim folder As Folder
Set folder = fso.GetFolder(FolderPath)
Dim file As File
For Each file In folder.Files
Dim newRow As ListRow
Set newRow = tbl.ListRows.Add
newRow.Range.Cells(1, 1).Value = file.Name
newRow.Range.Cells(1, 2).Value = file.Path
Next
Dim subFolder As Folder
For Each subFolder In folder.SubFolders
TraverseSubfolders subFolder.Path, tbl
Next
End Sub六、完整代碼
Sub TraverseFolders()
Dim MainFolder As String
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:B1"), , xlYes)
tbl.Name = "FileList"
tbl.HeaderRowRange.Cells(1, 1).Value = "File Name"
tbl.HeaderRowRange.Cells(1, 2).Value = "Path"
TraverseSubfolders MainFolder, tbl
End Sub
Sub TraverseSubfolders(FolderPath As String, tbl As ListObject)
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim folder As Folder
Set folder = fso.GetFolder(FolderPath)
Dim file As File
For Each file In folder.Files
Dim newRow As ListRow
Set newRow = tbl.ListRows.Add
newRow.Range.Cells(1, 1).Value = file.Name
newRow.Range.Cells(1, 2).Value = file.Path
Next
Dim subFolder As Folder
For Each subFolder In folder.SubFolders
TraverseSubfolders subFolder.Path, tbl
Next
End Sub七、總結
我們通過使用VBA內置的FileSystemObject對象，在遍歷文件夾中的所有文件時實現了遞歸遍歷所有子文件夾的功能，將所有文件名和路徑存儲在ListObject表格中。在實際應用中，我們可以進一步擴展這個功能，比如篩選指定類型的文件、添加文件屬性和標簽、自動處理重復文件等等。
京公網安備 11010802030320號