文章背景:某台仪器测量结束后,测试数据以pdf格式保存在日期文件夹内。想要通过获取12月份的pdf总数,计算当月产量。需要遍历各个日期文件夹,进行文件的统计汇总工作。
文件夹的架构如下:
采用VBA进行统计汇总工作,Userform的布局如下:
统计文件个数的按钮为CommandButton1,相关代码如下:
Option ExplicitPrivate Sub CommandButton1_Click() '遍历查找 Dim tarSheet As Worksheet, num As Integer, folder As String Dim flag As Long '1 清除原有数据 Set tarSheet = ThisWorkbook.Worksheets("统计文件个数") num = tarSheet.Range("A65535").End(xlUp).row If num > 1 Then tarSheet.Range("A2:B" & num).ClearContents End If '2 文件统计 folder = PathBox1.Value searchfile folder, tarSheet MsgBox "Done!" Exit SubEnd SubSub searchfile(folder As String, tarSheet As Worksheet) '文件统计 Dim fso As Object, fld As Object, subfld As Object, file As Object Dim row As Integer, temp As Integer row = 1 '行数追踪 Set fso = CreateObject("Scripting.FileSystemObject") If fso.FolderExists(folder) Then '判断文件夹是否存在 Set fld = fso.GetFolder(folder) For Each subfld In fld.SubFolders '遍历子文件夹 row = row + 1 '日期 tarSheet.Cells(row, 1) = subfld.Name temp = 0 For Each file In subfld.Files '遍历子文件 If file.Name Like "*.pdf" Then temp = temp + 1 End If Next '文件个数 tarSheet.Cells(row, 2) = temp Next Else MsgBox "文件夹的路径不存在,请确认!" Exit Sub End If tarSheet.Cells(row + 1, 1) = "总和" tarSheet.Cells(row + 1, 2) = Application.WorksheetFunction.Sum(tarSheet.Range("B2:B" & row)) End SubPrivate Sub UserForm_Initialize() '初始化设置 PathBox1.Value = "E:\12月份" End Sub
运行效果:
延伸阅读:
[1] VBA: 遍历文件抓取指定条件的数据