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一键按条件追加写入指定工作簿(自动新增行,不覆盖原有数据)
适用场景:
工厂经常要汇总多份报表、核对台账字段、做系统导入、统一模板。几十上百个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开发,每日持续分享办公实用技巧,收藏关注不迷路















































































