|
本帖最后由 脚毛乱了 于 2020-5-31 11:59 编辑
这条代码只读取一个源文件uvuv.xlsx数据,在执行的时候可以看到它反复读取关闭源文件,感觉可以一次提取完的
Sub test()
Dim Arr, myPath$, myName$, Arrl, Myr&, rq
Application.ScreenUpdating = False
Sheet1.Activate
rq = Sheet1.Range("C3")
[j3:j5].ClearContents
myPath = ThisWorkbook.Path & "\"
myName = Dir(myPath & "uvuv.xlsx")
Do While myName <> ""
With GetObject(myPath & myName)
Arrl = .Sheets(1).UsedRange: n = 2
For i = 2 To UBound(Arrl)
If rq = Arrl(i, 4) And Sheet1.Range("Z3") = Arrl(i, 3) Then
n = n + 1
Cells(n, 10) = Arrl(i, 5)
End If
Next
.Close False
End With
myName = Dir
Loop
[j6:j8].ClearContents
myPath = ThisWorkbook.Path & "\"
myName = Dir(myPath & "uvuv.xlsx")
Do While myName <> ""
With GetObject(myPath & myName)
Arrl = .Sheets(1).UsedRange: n = 5
For i = 2 To UBound(Arrl)
If rq = Arrl(i, 4) And Sheet1.Range("Z6") = Arrl(i, 3) Then
n = n + 1
Cells(n, 10) = Arrl(i, 5)
End If
Next
.Close False
End With
myName = Dir
Loop
[j9:j11].ClearContents
myPath = ThisWorkbook.Path & "\"
myName = Dir(myPath & "uvuv.xlsx")
Do While myName <> ""
With GetObject(myPath & myName)
Arrl = .Sheets(1).UsedRange: n = 8
For i = 2 To UBound(Arrl)
If rq = Arrl(i, 4) And Sheet1.Range("Z9") = Arrl(i, 3) Then
n = n + 1
Cells(n, 10) = Arrl(i, 5)
End If
Next
.Close False
End With
myName = Dir
Loop
[j12:j14].ClearContents
myPath = ThisWorkbook.Path & "\"
myName = Dir(myPath & "uvuv.xlsx")
Do While myName <> ""
With GetObject(myPath & myName)
Arrl = .Sheets(1).UsedRange: n = 11
For i = 2 To UBound(Arrl)
If rq = Arrl(i, 4) And Sheet1.Range("Z12") = Arrl(i, 3) Then
n = n + 1
Cells(n, 10) = Arrl(i, 5)
End If
Next
.Close False
End With
myName = Dir
Loop
[j15:j17].ClearContents
myPath = ThisWorkbook.Path & "\"
myName = Dir(myPath & "uvuv.xlsx")
Do While myName <> ""
With GetObject(myPath & myName)
Arrl = .Sheets(1).UsedRange: n = 14
For i = 2 To UBound(Arrl)
If rq = Arrl(i, 4) And Sheet1.Range("Z15") = Arrl(i, 3) Then
n = n + 1
Cells(n, 10) = Arrl(i, 5)
End If
Next
.Close False
End With
myName = Dir
Loop
|
|