|
Sub 导入文件()
Application.ScreenUpdating = False
Dim wb As Workbook, Sht As Worksheet, xRng As Range
arr = [a2:o2]
ReDim brr(1 To 100, 1 To UBound(arr, 2))
r = [a65536].End(3).Row + 1 '需要导入数据的起始行
s = 0
'nn = Val(Cells(r - 1, 1)) '已有数据的序号
'On Error Resume Next
pa = Split(ThisWorkbook.Path, "\")
For i = 0 To UBound(pa) - 1
XPath = XPath & pa(i) & "\"
Next
zdir XPath '递归获得本文件上一级文件夹内所有子文件夹内文件名,放入数组w Application.ScreenUpdating = False
Set d = CreateObject("scripting.dictionary")
For i = 1 To s
fn = w(i) '要打开的文件名
If InStr(fn, ThisWorkbook.Name) = 0 And fn Like "*.xls*" Then '如果和本文件名不同,那么打开文件,开始导入
Set wb = Workbooks.Open(fn)
Set Sht = wb.Worksheets(1)
xrr = Split(fn, "\")
yf = xrr(UBound(xrr) - 1) '月份
dq = xrr(UBound(xrr) - 2) '地区
x = yf & dq '月份+地区作为key,用于汇总分类
If Not d.exists(x) Then
n = n + 1
d(x) = n
brr(n, 1) = n: brr(n, 2) = yf: brr(n, 3) = dq
End If
p = d(x)
For j = 4 To UBound(arr, 2)
x = arr(1, j) '要查找的内容
Set xRng = Sht.UsedRange.Find(x, lookat:=xlWhole)
If Not xRng Is Nothing Then
If j > 9 Then brr(p, j) = brr(p, j) + xRng.Offset(0, 1) Else brr(p, j) = brr(p, j) + xRng.Offset(1, 0)
End If
Next
wb.Close False
End If
Next
Set Sht = Nothing
If n > 0 Then Cells(r, 1).Resize(n, UBound(brr, 2)) = brr
Application.ScreenUpdating = True
End Sub |
|