一、建立工程
首先,我們需要建立一個新的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表格中。在實際應用中,我們可以進一步擴展這個功能,比如篩選指定類型的文件、添加文件屬性和標簽、自動處理重復文件等等。