|
更正 漏了 补上 sqls +
諒橾呇 夔堆滇艘珨艘鎘ˋ 睡蚚EXCEL VBA SQL逄曆刉壺桶笢嗣豻腔笭葩暮翹ㄗ嗣跺趼僇ㄘ硐悵隱珨沭
Option Explicit
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sqls As String
Dim i As Integer
Sub SQLDELEDUPLICATERECORD()
'' Excel VBA SQL 刉壺桶笢嗣豻腔笭葩暮翹嗣跺趼僇ㄘ硐悵隱珨沭
SQLTEST = "SQLTEST.xls"
Workbooks(SQLTEST).Worksheets(1).Activate
MYPath = "D:\Users\Administrator\Documents\"
DBSFullName = MYPath + SQLTEST
If cnn.State = adStateOpen Then
cnn.Close
Set cnn = Nothing
End If
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & DBSFullName ''ThisWorkbook.FullName
If cnn.State = adStateOpen Then
'' 眕狟謗笱 SQL逄曆飲囮啖 諒睡党蜊
sqls = ""
sqls = sqls + "delete from [Sheet1$] a "
sqls = sqls + "where (a.PurchaseOrderNo,a.OrderDate) in (select PurchaseOrderNo,OrderDate from [Sheet1$] group by PurchaseOrderNo,OrderDate having count(*) > 1) "
sqls = sqls + "and PurchaseOrderNo not in (select min(PurchaseOrderNo) from [Sheet1$] group by PurchaseOrderNo,OrderDate having count(*)>1)"
cnn.Execute sqls
sqls = ""
sqls = sqls + "delete * from [Sheet1$] " '' <--- all other fields, except PurchaseOrderNo --->
sqls = sqls + "where PurchaseOrderNo NOT in (select min(PurchaseOrderNo) from [Sheet1$] group by IDNo,OrderDate,OrderFirmCode,BuyerName,BuyerEmail,SupplierNo,PaymentTermsNET,FreightTerms,Transportation,ShipVia,Destination)" ''' having count(*)>1
cnn.Execute sqls
End If
If cnn.State = adStateOpen Then
cnn.Close
Set cnn = Nothing
End If
If rst.State Then rst.Close
If rst.State = 1 Then rst.Close
Set rst = Nothing
End Sub
ROBERTCHENTPE
|
|