ARTICLE · 1373104
Excel-vba递归遍历子文件夹版本(抓取所有子文件夹内Excel,提取文件名、工作表名、字段清单)
Excel-vba递归遍历子文件夹版本(抓取所有子文件夹内Excel,提取文件名、工作表名、字段清单)
最近很多人对之前发的提取文件夹里所有Excel文件名的文章,想要获取完整的所有文件名,对此特意进行了升级
改动核心:原版只扫当前选中文件夹;这个版本递归往下钻所有子文件夹,多层嵌套全部读取,工厂多层归档台账文件夹非常好用
数组处理、错误捕获、跳过打不开/损坏文件、跳过宏冲突,实测稳定

完整递归VBA代码
vba
Option Explicit
Dim outWs As Worksheet
Dim writeRow As Long
Sub 递归提取所有Excel信息_含子文件夹()
Dim fd As FileDialog
Dim selPath As String
Application.ScreenUpdating = False
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.Title = "选择根文件夹(会自动遍历里面全部子文件夹)"
If fd.Show <> -1 Then
MsgBox "未选择文件夹,退出", vbInformation
GoTo Clean
End If
selPath = fd.SelectedItems(1)
'输出表初始化
On Error Resume Next
Application.DisplayAlerts = False
Sheets("文件清单结果").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set outWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
outWs.Name = "文件清单结果"
writeRow = 2
'表头
outWs.Range("A1:E1") = Array("完整路径", "文件名", "工作表名", "表头字段", "备注")
'启动递归
Call ScanFolder(selPath)
outWs.Columns("A:E").AutoFit
MsgBox "✅ 扫描完成!一共提取 " & writeRow - 2 & " 条工作表信息", vbInformation
Clean:
Application.ScreenUpdating = True
Set fd = Nothing
Set outWs = Nothing
End Sub
'递归核心过程,遍历文件夹+子文件夹
Private Sub ScanFolder(fPath As String)
Dim fso As Object
Dim fld As Object
Dim subFld As Object
Dim file As Object
Dim ext As String
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(fPath)
'先遍历当前文件夹里面的文件
For Each file In fld.Files
ext = LCase(fso.GetExtensionName(file.Path))
If ext = "xlsx" Or ext = "xlsm" Or ext = "xls" Then
Call ReadExcelFile(file.Path)
End If
Next file
'递归:循环所有子文件夹,继续往下扫
For Each subFld In fld.SubFolders
Call ScanFolder(subFld.Path)
Next subFld
Set fso = Nothing
End Sub
'读取单个Excel:提取表名、第一行表头字段
Private Sub ReadExcelFile(fullPath As String)
Dim wb As Workbook
Dim sh As Worksheet
Dim cell As Range
Dim fieldStr As String
On Error GoTo ErrRead
Set wb = Workbooks.Open(Filename:=fullPath, ReadOnly:=True, UpdateLinks:=False)
For Each sh In wb.Worksheets
fieldStr = ""
'读取第1行表头,非空字段拼接
For Each cell In sh.Rows(1).Cells
If Trim(cell.Value) <> "" Then
fieldStr = fieldStr & "|" & Trim(cell.Value)
Else
Exit For '第一行连续空就停止,避免整行空白一直读
End If
Next cell
If fieldStr <> "" Then fieldStr = Mid(fieldStr, 3)
'写入结果表
outWs.Cells(writeRow, "A").Value = fullPath
outWs.Cells(writeRow, "B").Value = sh.Parent.Name
outWs.Cells(writeRow, "C").Value = sh.Name
outWs.Cells(writeRow, "D").Value = fieldStr
outWs.Cells(writeRow, "E").Value = "正常"
writeRow = writeRow + 1
Next sh
wb.Close SaveChanges:=False
Exit Sub
ErrRead:
outWs.Cells(writeRow, "A").Value = fullPath
outWs.Cells(writeRow, "B").Value = "读取失败"
outWs.Cells(writeRow, "C").Value = ""
outWs.Cells(writeRow, "D").Value = ""
outWs.Cells(writeRow, "E").Value = Err.Description
writeRow = writeRow + 1
End Sub
说明:
✅ 递归版本新增能力
1. 支持多层嵌套子文件夹,不管文件夹套几层,全部自动遍历
2. 区分:xlsx / xlsm / xls 三种格式,只读打开,不会改动源文件
3. 读取失败单独标记备注列:文件损坏、密码加密、打开报错都会写清楚,不会直接崩溃
4. 自动新建【文件清单结果】工作表汇总,A列保留完整路径,方便定位文件
📌 和原版(仅单层文件夹)的区别
- 旧版:只读取你选中文件夹里的Excel,里面子文件夹直接跳过
- 递归版:从你选的根目录开始,深度遍历所有子目录,适合工厂按月份、班组分类归档的台账文件夹

⚠️每日持续分享办公自动化实用技巧, 专业vba定制开发。关注收藏不迷路。
标题:Excel高级录入窗体查询+修改本条记录 升级版,录完自动追加台账 Excel-vba一键整行高亮异常数据|产能不达标、不良率超标直接标红,不用反复设置条件格式 Excel-vba台账怕崩、文件损坏?定时自动备份,后台静默存副本 Excel-VBA-批量重命名文件夹内文件:读取表格新旧文件名,批量修改Excel/图片 Excel-VBA数据太脏不用手改!一键全自动清洗:去空格、清乱码、统一日期、多条件去重
最近很多人对之前发的提取文件夹里所有Excel文件名的文章,想要获取完整的所有文件名,对此特意进行了升级
改动核心:原版只扫当前选中文件夹;这个版本递归往下钻所有子文件夹,多层嵌套全部读取,工厂多层归档台账文件夹非常好用
数组处理、错误捕获、跳过打不开/损坏文件、跳过宏冲突,实测稳定

完整递归VBA代码
vba
Option Explicit
Dim outWs As Worksheet
Dim writeRow As Long
Sub 递归提取所有Excel信息_含子文件夹()
Dim fd As FileDialog
Dim selPath As String
Application.ScreenUpdating = False
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
fd.Title = "选择根文件夹(会自动遍历里面全部子文件夹)"
If fd.Show <> -1 Then
MsgBox "未选择文件夹,退出", vbInformation
GoTo Clean
End If
selPath = fd.SelectedItems(1)
'输出表初始化
On Error Resume Next
Application.DisplayAlerts = False
Sheets("文件清单结果").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set outWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
outWs.Name = "文件清单结果"
writeRow = 2
'表头
outWs.Range("A1:E1") = Array("完整路径", "文件名", "工作表名", "表头字段", "备注")
'启动递归
Call ScanFolder(selPath)
outWs.Columns("A:E").AutoFit
MsgBox "✅ 扫描完成!一共提取 " & writeRow - 2 & " 条工作表信息", vbInformation
Clean:
Application.ScreenUpdating = True
Set fd = Nothing
Set outWs = Nothing
End Sub
'递归核心过程,遍历文件夹+子文件夹
Private Sub ScanFolder(fPath As String)
Dim fso As Object
Dim fld As Object
Dim subFld As Object
Dim file As Object
Dim ext As String
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder(fPath)
'先遍历当前文件夹里面的文件
For Each file In fld.Files
ext = LCase(fso.GetExtensionName(file.Path))
If ext = "xlsx" Or ext = "xlsm" Or ext = "xls" Then
Call ReadExcelFile(file.Path)
End If
Next file
'递归:循环所有子文件夹,继续往下扫
For Each subFld In fld.SubFolders
Call ScanFolder(subFld.Path)
Next subFld
Set fso = Nothing
End Sub
'读取单个Excel:提取表名、第一行表头字段
Private Sub ReadExcelFile(fullPath As String)
Dim wb As Workbook
Dim sh As Worksheet
Dim cell As Range
Dim fieldStr As String
On Error GoTo ErrRead
Set wb = Workbooks.Open(Filename:=fullPath, ReadOnly:=True, UpdateLinks:=False)
For Each sh In wb.Worksheets
fieldStr = ""
'读取第1行表头,非空字段拼接
For Each cell In sh.Rows(1).Cells
If Trim(cell.Value) <> "" Then
fieldStr = fieldStr & "|" & Trim(cell.Value)
Else
Exit For '第一行连续空就停止,避免整行空白一直读
End If
Next cell
If fieldStr <> "" Then fieldStr = Mid(fieldStr, 3)
'写入结果表
outWs.Cells(writeRow, "A").Value = fullPath
outWs.Cells(writeRow, "B").Value = sh.Parent.Name
outWs.Cells(writeRow, "C").Value = sh.Name
outWs.Cells(writeRow, "D").Value = fieldStr
outWs.Cells(writeRow, "E").Value = "正常"
writeRow = writeRow + 1
Next sh
wb.Close SaveChanges:=False
Exit Sub
ErrRead:
outWs.Cells(writeRow, "A").Value = fullPath
outWs.Cells(writeRow, "B").Value = "读取失败"
outWs.Cells(writeRow, "C").Value = ""
outWs.Cells(writeRow, "D").Value = ""
outWs.Cells(writeRow, "E").Value = Err.Description
writeRow = writeRow + 1
End Sub
✅ 递归版本新增能力
1. 支持多层嵌套子文件夹,不管文件夹套几层,全部自动遍历
2. 区分:xlsx / xlsm / xls 三种格式,只读打开,不会改动源文件
3. 读取失败单独标记备注列:文件损坏、密码加密、打开报错都会写清楚,不会直接崩溃
4. 自动新建【文件清单结果】工作表汇总,A列保留完整路径,方便定位文件
📌 和原版(仅单层文件夹)的区别
- 旧版:只读取你选中文件夹里的Excel,里面子文件夹直接跳过
- 递归版:从你选的根目录开始,深度遍历所有子目录,适合工厂按月份、班组分类归档的台账文件夹

⚠️每日持续分享办公自动化实用技巧, 专业vba定制开发。关注收藏不迷路。















































































