|
- Dim w(1 To 10000), s%
- Sub 导入文件()
- Application.ScreenUpdating = False
- Dim Filename, wb As Workbook, Sht As Worksheet, xRng As Range
- Filename = Dir(ThisWorkbook.Path & "\*.xls")
- arr = [a2:n2]
- ReDim brr(1 To 100, 1 To UBound(arr, 2))
- r = [a65536].End(3).Row + 1 '需要导入数据的起始行
- nn = Val(Cells(r - 1, 1)) '已有数据的序号
- On Error Resume Next
- s = 0
- zdir ThisWorkbook.Path & "" '递归获得本文件夹及所有子文件夹内文件名,放入数组w
- Application.ScreenUpdating = False
- For i = 1 To s
- fn = w(i) '要打开的文件名
- If InStr(fn, ThisWorkbook.Name) = 0 Then '如果和本文件名不同,那么打开文件,开始导入
- Set wb = Workbooks.Open(fn)
- Set Sht = wb.Worksheets(1)
- n = n + 1
- brr(n, 1) = nn + n: brr(n, 2) = Split(fn, "")(UBound(Split(fn, "")) - 1): brr(n, 3) = Sht.[c2]: brr(n, 4) = Sht.[M2]
- For j = 5 To UBound(arr, 2)
- x = arr(1, j) '要查找的内容
- Set xRng = Sht.UsedRange.Find(x, lookat:=xlWhole)
- If Not xRng Is Nothing Then
- If j <= UBound(arr, 2) - 6 Then brr(n, j) = xRng.Offset(0, 1) Else brr(n, j) = xRng.Offset(1, 0)
- End If
- Next
- wb.Close False
- End If
- Next
- Set Sht = Nothing
- Cells(r, 1).Resize(n, UBound(brr, 2)) = brr
- Application.ScreenUpdating = True
- End Sub
- Sub zdir(p) '递归获得本文件夹及所有子文件夹内文件名
- Set fs = CreateObject("scripting.filesystemobject")
- For Each f In fs.GetFolder(p).Files
- If f <> ThisWorkbook.FullName Then s = s + 1: w(s) = f
- Next
- For Each m In fs.GetFolder(p).SubFolders
- zdir m
- Next
- End Sub
复制代码 |
|