|
Sub 汇总2()
Dim Wbk As Workbook, Fn$, myPath$, cnn, k&, Sht As Worksheet
Dim st As Worksheet
Dim s, t$, Sql$
Set Sht = Sheet1
Sht.Activate
myPath = ThisWorkbook.Path & "/"
Fn = Dir(myPath & "*.xl*")
Application.ScreenUpdating = False
s = "[" & Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose([B7:U7])), "],[") & "]"
[B8:U65536] = ""
[v1:w65536] = ""
Do While Fn <> ""
If Fn <> ThisWorkbook.Name Then
Set Wbk = Workbooks.Open(myPath & Fn)
With Wbk
For Each st In Sheets
With st
For k = 8 To Sht.Range("a65536").End(xlUp).Row
t = Sht.Range("a" & k).Value
If Len(t) Then
c = st.Name
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';Data Source=" & myPath & Fn
Sql = "SELECT " & s & " FROM [" & c & "$a2:x] WHERE 姓名 ='" & t & "'"
Sht.Range("b65536").End(xlUp)(2).CopyFromRecordset cnn.Execute(Sql)
Sht.Range("v65536").End(xlUp)(2) = Fn
Sht.Range("w65536").End(xlUp)(2) = st.Name
cnn.Close
Set cnn = Nothing
Else
MsgBox "无提取人!", 64
End If
Next
End With
Next
Application.DisplayAlerts = False
.Close False
Application.DisplayAlerts = True
End With
End If
Fn = Dir
Loop
Application.ScreenUpdating = True
End Sub
|
评分
-
1
查看全部评分
-
|