夜雨聆风学习资料网

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数据太脏不用手改!一键全自动清洗:去空格、清乱码、统一日期、多条件去重

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料