| 网站首页 | 业界新闻 | 小组 | 交易 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛

Sub xx()
Dim Cn As Object, ar, i&, p\$, f\$, Sq\$(2), s\$
Application.ScreenUpdating = False
If Application.Version < 12 Then
s = "Excel 8.0;Database="
Cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
Else
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
s = "Excel 12.0;Database="
End If
With Worksheets(1)
.Activate
Sq(0) = "[" & .Name & "\$g1:g" & .Cells(.Rows.Count, "g").End(xlUp).Row & "]a"
End With
p = ThisWorkbook.Path & "\"
ar = Array("scra.xls", "SHIPP.xls")
For i = 0 To UBound(ar)
f = p & ar(i)
If Dir(f, vbDirectory) <> "" Then
Sq(1) = Sq(1) & " UNION ALL SELECT 條碼,代碼,狀態,時間 FROM [" & s & f & "].[\$A1:N] WHERE 條碼 IS NOT NULL"
End If
Next
Sq(1) = "SELECT * FROM (" & Mid(Sq(1), 12) & ") ORDER BY 時間"
Sq(1) = "SELECT 條碼,LAST(代碼) AS 代碼,LAST(狀態) AS 狀態,MAX(時間) AS 時間 FROM (" & Sq(1) & ") GROUP BY 條碼"
Sq(2) = "SELECT b.代碼 FROM " & Sq(0) & " LEFT JOIN (" & Sq(1) & ")b ON a.條碼=b.條碼"
Range("i2").CopyFromRecordset Cn.Execute(Sq(2))
Cn.Close
Set Cn = Nothing
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
End Sub

[此贴子已经被作者于2020-7-8 00:05编辑过]

Dim Cn As Object, ar, i&, p\$, f\$, Sq\$(2), s\$, qstr\$   'qstr -- 淏腔脤戙逄曆
qstr = "select ee.代碼 from  [Sheet1\$g1:g1000]  as aa left join ( select  bb.條碼,bb.代碼,cc.sj as 時間 from  " & _
"(SELECT 條碼,代碼,狀態,時間 FROM [Excel 12.0;Database=C:\Users\X\Desktop\TEST\scra.xls].[\$A1:N] " & _
"            WHERE 條碼 IS NOT NULL UNION ALL " & _
"            SELECT 條碼,代碼,狀態,時間 FROM [Excel 12.0;Database=C:\Users\X\Desktop\TEST\SHIPP.xls].[\$A1:N]  " & _
"            WHERE 條碼 IS NOT NULL) as bb inner join  (SELECT 條碼, max(時間) as sj FROM  ( " & _
"SELECT 條碼,代碼,狀態,時間 FROM [Excel 12.0;Database=C:\Users\X\Desktop\TEST\scra.xls].[\$A1:N]  " & _
"WHERE 條碼 IS NOT NULL      UNION ALL  " & _
"            SELECT 條碼,代碼,狀態,時間 FROM [Excel 12.0;Database=C:\Users\X\Desktop\TEST\SHIPP.xls].[\$A1:N]  " & _
"            WHERE 條碼 IS NOT NULL) group by 條碼 ) as cc   " & _
"on bb.條碼  = cc.條碼  and  bb.時間 = cc.sj) as ee on aa.條碼  = ee.條碼  "

1、把TT中填入的多个条码装入一个数组。
2、依次打开 shipp/scra/.... 等文件，在文件中查找有无与数组中的条码相同的，

（至于如何查找更高效？用Find()方法，还是直接在当前表中用SQL查询?
看你自己觉得哪种方式更好,一般相同条码有很多条记录的，我觉得用SQL查询更好，如果记录量不大，用FIND更好。)
3、打开下一个文件，查找，如有且时间更近，则替换新的代码进入数组，关闭文件。
4、遍历所有文件，直到完毕（此时窗口中应该只有主文件TT）。
5、把数组采集到的数据，填写入TT的列中，释放数组。

• 7
• 1/1页
• 1