张雄友 发表于 2015-3-21 05:08
提取后一共是15行数据,怎么有75行?是用 open 方法遍历文件夹的。
Sub 提取每个表最后一行总数据()
Dim mypath$, wj$, wb As Workbook, i&, j&, sh As Worksheet
Dim arr, brr(1 To 60000, 1 To 9), s&
[A2:I65536].ClearContents
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = ThisWorkbook.Path & "\"
If .Show = False Then Exit Sub
mypath = .SelectedItems(1) & "\"
End With
Set w = Application.WorksheetFunction
s = 0
Filepath = GetName(mypath)
For kk = 0 To UBound(Filepath)
Set wb = Workbooks.Open(Filepath(kk))
For Each sh In wb.Sheets
If w.CountA(sh.UsedRange) Then
arr = sh.UsedRange
n = UBound(arr): s = s + 1
brr(s, 1) = s: brr(s, 2) = arr(1, 1)
For j = 3 To UBound(arr, 2)
brr(s, j) = arr(n, j)
Next
End If
Next
wb.Close 0
Next
Range("D2").Resize(s, UBound(brr, 2)) = brr
Application.ScreenUpdating = True
End Sub
Function GetName(lj As String)
Dim MyName, dic, Did, i, t, F, tt, MyFileName
Set dic = CreateObject("Scripting.Dictionary")
Set Did = CreateObject("Scripting.Dictionary")
dic.Add (lj), ""
i = 0
Do While i < dic.Count
Ke = dic.Keys
MyName = Dir(Ke(i), vbDirectory)
Do While MyName <> ""
If MyName <> "." And MyName <> ".." Then
If (GetAttr(Ke(i) & MyName) And vbDirectory) = vbDirectory Then
dic.Add (Ke(i) & MyName & "\"), ""
End If
End If
MyName = Dir
Loop
i = i + 1
Loop
For Each Ke In dic.Keys
MyFileName = Dir(Ke & "*.xls*")
Do While MyFileName <> ""
If MyFileName <> ThisWorkbook.Name Then Did.Add (Ke & MyFileName), ""
MyFileName = Dir
Loop
Next
GetName = Did.Keys
End Function