- 帖子
- 354
- 主題
- 5
- 精華
- 0
- 積分
- 387
- 點名
- 0
- 作業系統
- windows7
- 軟體版本
- vba,vb,excel2007
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2017-1-8
- 最後登錄
- 2024-8-2
 
|
回復 1# ssooi
兩種寫法 自己挑一個
Sub t1()
o = Split("Provider=Microsoft.,,.0;Extended Properties=Excel ,,.0;Data Source=" & ThisWorkbook.FullName, ",")
Set cn = CreateObject("adodb.connection"): If Application.Version > 12 Then o(1) = "ACE.OLEDB.12": _
o(3) = 12: cn.Open Join(o, "") Else o(1) = "Jet.OLEDB.4": o(3) = 8: cn.Open Join(o, "")
q = "SELECT a.序號, a.時程, a.牌子 FROM [工作表2$A1:C] as a "
q = q & "WHERE EXISTS ( SELECT 1 FROM [工作表2$A1:C] AS b "
q = q & " WHERE b.序號 = a.序號 AND b.牌子 = a.牌子 "
q = q & " GROUP BY b.序號, b.牌子 HAVING COUNT(*) = 1)"
[G:J].ClearContents: [G1].CopyFromRecordset cn.Execute(q)
End Sub
Sub t2()
o = Split("Provider=Microsoft.,,.0;Extended Properties=Excel ,,.0;Data Source=" & ThisWorkbook.FullName, ",")
Set cn = CreateObject("adodb.connection"): If Application.Version > 12 Then o(1) = "ACE.OLEDB.12": _
o(3) = 12: cn.Open Join(o, "") Else o(1) = "Jet.OLEDB.4": o(3) = 8: cn.Open Join(o, "")
q = "SELECT a.序號, a.時程, a.牌子 FROM [工作表2$A1:C] AS a "
q = q & "WHERE ( SELECT COUNT(*) FROM [工作表2$A1:C] AS b "
q = q & " WHERE b.序號 = a.序號 AND b.牌子 = a.牌子) = 1"
[G:J].ClearContents: [G1].CopyFromRecordset cn.Execute(q)
End Sub |
-
-
TEST.zip
(15.54 KB)
|
發表於 2023-12-2 02:49
| 