|
Option Explicit
Sub test()
Dim Cn As Object, Rs As Object, p$, f$, Sq$, k%, i%
Cells.ClearContents
Application.ScreenUpdating = False
Set Cn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.Recordset")
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
p = ThisWorkbook.Path & "\"
f = Dir(p & "*.xls*")
Do While f <> ""
If f <> ThisWorkbook.Name Then
Sq = "SELECT * FROM [Excel 12.0;Database=" & p & f & "].[$A1:DJ] WHERE 代码=157"
Rs.Open Sq, Cn, 1, 3
k = k + 1
If k = 1 Then
For i = 0 To Rs.Fields.Count - 1
[a1].Offset(0, i) = Rs.Fields(i).Name
Next
End If
Cells(k + 1, 1).CopyFromRecordset Rs
If Rs.State = 1 Then Rs.Close
End If
f = Dir
Loop
Cn.Close
Set Cn = Nothing
Set Rs = Nothing
Application.ScreenUpdating = True
End Sub
|
评分
-
3
查看全部评分
-
|