数据导入,选取最近一笔时间数据

请问有大牛哥牛姐在吗？
小弟遇到一个sql查询问题
主档tt要导入shipp+scra数据
主档在G列输入条形码后，按鈕导入shipp+scra数据 I列代码中的内容，如果G列条形码重复的话，依照N列提取最近一笔时间为2020/7/7日，
可是一直都导入6/30那笔数据....(反橘色)
请问是那裡有问题？请大神们帮个忙修正一下数据，谢谢



Sub xx()
Dim Cn As Object, ar, i&, p$, f$, Sq$(2), s$
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set Cn = CreateObject("ADODB.Connection")
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编辑过]