在VBA中，遍历指定目录下的Excel文件通常涉及使用FileSystemObject。以下是一个基本的例子，说明如何遍历一个目录并列出所有的Excel文件（假设Excel文件的扩展名为.xlsx或.xls）：
首先，你需要确保你的VBA项目引用了Microsoft Scripting Runtime。你可以通过以下步骤添加这个引用：
- 在VBA编辑器中，选择工具 > 引用。
- 在弹出的对话框中，滚动到Microsoft Scripting Runtime并勾选它。
- 点击确定。
代码
参考代码如下，可以实现递归遍历子文件夹：
Sub ListFilesInFolderRecursively()
Dim MyFolder As String
Dim wb As Workbook
Dim ws As Worksheet
Dim r As Range
Dim rowNum As Long
' 设置要遍历的文件夹路径
MyFolder = "C:\YourFolderPath\"
' 创建一个新的工作簿来记录结果
Set wb = Workbooks.Add
Set ws = wb.Sheets(1)
rowNum = 1
' 调用递归函数来遍历文件夹
Call ListFilesInFolderRec(MyFolder, ws, rowNum)
' 自动调整列宽以适应内容
ws.Columns(1).AutoFit
' 清理对象
Set r = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
Sub ListFilesInFolderRec(FolderPath As String, ws As Worksheet, ByRef rowNum As Long)
Dim FSO As Object
Dim Folder As Object
Dim SubFolder As Object
Dim File As Object
' 创建FileSystemObject对象
Set FSO = CreateObject("Scripting.FileSystemObject")
' 获取文件夹对象
If FSO.FolderExists(FolderPath) Then
Set Folder = FSO.GetFolder(FolderPath)
' 遍历文件夹中的所有文件
For Each File In Folder.Files
' 检查文件是否是Excel文件
If LCase(FSO.GetExtensionName(File.Name)) = "xlsx" Or LCase(FSO.GetExtensionName(File.Name)) = "xls" Then
' 在工作簿中记录文件名
ws.Cells(rowNum, 1).Value = File.Path
rowNum = rowNum + 1
End If
Next File
' 遍历子文件夹
For Each SubFolder In Folder.SubFolders
' 递归调用此函数来处理子文件夹
Call ListFilesInFolderRec(SubFolder.Path, ws, rowNum)
Next SubFolder
Else
MsgBox "The folder path does not exist."
End If
' 清理对象
Set File = Nothing
Set SubFolder = Nothing
Set Folder = Nothing
Set FSO = Nothing
End Sub
ListFilesInFolderRecursively 是主函数，它创建一个新的工作簿来记录文件路径。然后它调用 ListFilesInFolderRec 函数来递归地遍历指定目录及其所有子目录。ListFilesInFolderRec 函数检查每个文件是否是Excel文件，如果是，则将其路径记录到工作簿中。同时，它还遍历每个子文件夹，并递归调用自身来处理这些子文件夹。
注意，在代码中我使用了 LCase 函数来确保扩展名比较时不受大小写影响。同时，我也记录了文件的完整路径（File.Path），而不仅仅是文件名，这样你可以清楚地知道文件位于哪个文件夹中。如果你只需要文件名，你可以将 ws.Cells(rowNum, 1).Value = File.Path 更改为 ws.Cells(rowNum, 1).Value = File.Name