|
这么办- Sub 导入()
- 'http://www.excelpx.com/forum-5-1.html
- Dim Wb As Workbook, FFF, F, Sh As Worksheet, Rng1 As Range, Rng2 As Range, Rng3 As Range
- Dim brr(1 To 10000, 1 To 5), crr()
- Set fso = CreateObject("scripting.filesystemobject")
- Set ff = fso.getfolder(ThisWorkbook.Path)
- For Each FFF In ff.subfolders
- nf = Replace(FFF, ff & "", "") '年份
- ReDim crr(1 To 12) '对应1--12月份,排序
- For Each F In FFF.Files
- yf = Val(Split(F, "")(UBound(Split(F, "")))) '月份
- crr(yf) = F '按月份放到数组对应位置
- Next
- For p = 1 To 12
- F = crr(p)
- If Len(F) > 0 Then
- Set Wb = Workbooks.Open(F)
- gzb = Replace(Wb.Name, ".xlsx", "") '工作簿名(加年份)
- For Each Sh In Wb.Worksheets '''''''''''''''''''''''''''''''''''''''''''''遍历所有表
- Set Rng1 = Sh.UsedRange.Find("工号")
- Set Rng2 = Sh.UsedRange.Find("姓名")
- Set Rng3 = Sh.UsedRange.Find("实发工资")
- If Not Rng1 Is Nothing And Not Rng2 Is Nothing And Not Rng3 Is Nothing Then
- c1 = Rng1.Column
- c2 = Rng2.Column
- c3 = Rng3.Column
- r = Sh.Cells(65536, c3).End(3).Row '实发工资列的最大行
- arr = Sh.[a1].Resize(r, Application.Max(c1, c2, c3)) '定义数组
- For i = Rng1.Row + 1 To UBound(arr) '表头行+1 开始循环
- If Len(arr(i, c1) & arr(i, c2)) > 0 Then
- n = n + 1
- brr(n, 1) = nf & "" & Wb.Name
- brr(n, 2) = Sh.Name
- brr(n, 3) = arr(i, c1)
- brr(n, 4) = arr(i, c2)
- brr(n, 5) = arr(i, c3)
- End If
- Next
- End If
- Next '''''''''''''''''''''''''''''''''''''''''''''''遍历所有表
- Wb.Close False
- End If
- Next
- Next
- With ActiveSheet
- .Columns("A:E").Clear
- .[a1].Resize(1, 5) = Array("工作簿名", "工作表名", "工号", "姓名", "实发工资")
- .[a2].Resize(n, 5) = brr
- .[a1].CurrentRegion.Borders.LineStyle = 1
- .[a1].CurrentRegion.Columns.AutoFit
- End With
- End Sub
复制代码 |
评分
-
查看全部评分
|