|
- Sub 合并数据()
- Dim r As Long, c As Long, rq, n&, i&
- r = 1 '表头的行数
- c = 9 '表头的列数
- rq = [k1].Value
- Dim FileName As String, wb As Workbook, sht As Worksheet, x, k As Long, fn, wbn As String, arr As Variant
- Dim num, sum, sums As Integer '记录条数
- n = 1
- Range(Cells(r + 1, "A"), Cells(65536, c)).ClearContents '清除汇总表原表数据
- Application.ScreenUpdating = False
- FileName = Dir(ThisWorkbook.Path & "\*.xlsx")
- Do While FileName <> ""
- If FileName <> ThisWorkbook.Name Then '判断文件是否是本工作薄
- sum = 0
- fn = ThisWorkbook.Path & "" & FileName
- Set wb = GetObject(fn) '将fn代表的工作薄对象赋给对象
- Set sht = wb.Worksheets(1) '汇总的是第一张工作表
- '将数据表中的记录保存到arr数组里
- arr = sht.Range(sht.Cells(r + 1, "A"), sht.Cells("65536", "B").End(xlUp).Offset(0, c))
- '将数组arr中的数据写入工作表中
- For i = 2 To UBound(arr)
- If arr(i, 1) = rq Then
- n = n + 1: sum = sum + 1
- Cells(n, 1).Resize(1, UBound(arr, 2)) = Application.Index(arr, i, 0)
- End If
- Next
- '把每个工作表中的数据条数汇总
- If sum > 0 Then
- sums = sum + sums: num = num + 1
- wbn = wbn & Chr(13) & wb.Name & "----------------------------【" & sum & "】"
- End If
- wb.Close False
- End If
- FileName = Dir
- Loop
- Application.ScreenUpdating = True
- If num > 0 Then MsgBox "共合并了【" & num & "】个工作薄下的【" & sums & "】条数据" & Chr(13) & wbn, vbInformation, "提示"
- End Sub
复制代码 |
|