夜雨聆风学习资料网

ARTICLE · 1361025

Excel-vba工厂台账太多乱成一团?一键抓取文件夹内所有Excel:文件名+工作表名+表头字段清单

Excel-vba工厂台账太多乱成一团?一键抓取文件夹内所有Excel:文件名+工作表名+表头字段清单

 适用场景:
工厂经常要汇总多份报表、核对台账字段、做系统导入、统一模板。几十上百个Excel,挨个打开复制表头,半天就没了。这个代码直接扫整个文件夹,自动把【文件名、工作表名称、所有表头字段】输出到一张总表里。

功能说明

✅ 选择目标文件夹,自动遍历里面所有  .xlsx/.xls 
✅ 输出内容:文件名、工作表名、A行表头(字段名)
✅ 遇到打不开/损坏文件自动跳过,标注错误
✅ 数组读取,速度快,不弹窗刷屏
✅ 结果直接生成在当前工作簿新建的【文件字段清单】工作表
✅ 不改动原文件,只读取信息,安全

VBA完整代码(直接复制可用)

vba    
Sub GetAllExcelFieldList()
    Dim fd As FileDialog
    Dim folderPath As String
    Dim fileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim arr As Variant
    Dim lastCol As Long
    Dim r As Long
    Dim outWs As Worksheet

    '关闭屏幕刷新、弹窗
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    '创建输出表
    On Error Resume Next
    Set outWs = ThisWorkbook.Worksheets("文件字段清单")
    On Error GoTo 0
    If outWs Is Nothing Then
        Set outWs = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        outWs.Name = "文件字段清单"
    End If
    outWs.Cells.Clear
    '表头
    outWs.Range("A1:D1") = Array("文件名", "工作表名称", "字段清单(逗号分隔)", "备注")
    r = 2

    '选择文件夹
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        .Title = "请选择存放Excel的文件夹"
        If .Show <> -1 Then
            MsgBox "未选择文件夹,退出", vbInformation
            GoTo EndSub
        End If
        folderPath = .SelectedItems(1) & "\"
    End With

    '遍历文件
    fileName = Dir(folderPath & "*.*")
    Do While fileName <> ""
        If LCase(Right(fileName, 4)) = ".xls" Or LCase(Right(fileName, 5)) = ".xlsx" Then
            On Error Resume Next
            Set wb = Workbooks.Open(Filename:=folderPath & fileName, ReadOnly:=True)
            If Err.Number <> 0 Then
                outWs.Cells(r, 1) = fileName
                outWs.Cells(r, 4) = "文件打开失败/损坏/被占用"
                r = r + 1
                Err.Clear
                GoTo NextFile
            End If
            On Error GoTo 0

            '遍历这个文件里所有工作表
            For Each ws In wb.Worksheets
                lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                If lastCol >= 1 Then
                    arr = ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol)).Value
                    outWs.Cells(r, 1) = fileName
                    outWs.Cells(r, 2) = ws.Name
                    outWs.Cells(r, 3) = Join(Application.Transpose(Application.Transpose(arr)), ",")
                Else
                    outWs.Cells(r, 1) = fileName
                    outWs.Cells(r, 2) = ws.Name
                    outWs.Cells(r, 3) = "无表头数据"
                End If
                r = r + 1
            Next ws
            wb.Close SaveChanges:=False
        End If
NextFile:
        fileName = Dir()
    Loop

    '自动调整列宽
    outWs.Columns("A:D").AutoFit
    MsgBox "提取完成!一共读取 " & r - 2 & " 条工作表信息,查看【文件字段清单】", vbInformation

EndSub:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Set fd = Nothing
    Set wb = Nothing
    Set ws = Nothing
    Set outWs = Nothing
End Sub


使用步骤

1. 新建一个空白Excel,按  Alt+F11  打开VBA编辑器
2. 插入【模块】,粘贴上面全部代码
3. 按F5运行  GetAllExcelFieldList 
4. 在弹窗里选中你存放报表的文件夹
5. 等待跑完,自动生成清单表

 子文件夹里面的文件默认不扫;如果你需要递归、连子文件夹一起抓取,我给你改一版

❤️专业vba开发,每日持续分享办公实用技巧,收藏关注不迷路
Excel-vba工作表太多翻到头疼?一键生成带超链接目录,点哪跳哪
Excel-vba一键整行高亮异常数据|产能不达标、不良率超标直接标红,不用反复设置条件格式
Excel-vba台账怕崩、文件损坏?定时自动备份,后台静默存副本
Excel-VBA月报报表挨个发邮件太耗时间!VBA读取表格,自动填正文、带上附件一键推送
Excel VBA一键按条件追加写入指定工作簿(自动新增行,不覆盖原有数据)

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

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

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

相关学习资料