PDA

View Full Version : Solved: find duplicate...



sal21
11-10-2004, 08:18 AM
This script export from Excel sheet into an MDB table.
My problem is:

To controll duplicate from column S of the sheet and filed SRVIZIO of the table TOTALE.
Column S and field servizio are unique ID

During the import into access if alreday existis in the field SERVIZIO a line identified with the same value of column S to be exported go to the next line of the sheet and not import the already line already existis into table of MDB...

http://www.gssitaly.com/sheet_totale.zip (http://www.gssitaly.com/sheet_totale.zip)
http://www.gssitaly.com/prova.zip (http://www.gssitaly.com/prova.zip)

I hope of to have been clear

Sub ADO_TOTALE1()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Dim rsFind As ADODB.Recordset
Set cn = New ADODB.Connection

Set rs = New Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\MACRO\L0785-AUT\PROVA.MDB;"
Set rs = New ADODB.Recordset
rs.Open "TOTALE", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 7 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
With rs
.AddNew ' create a new record
'add values to each field in the record
Sheets("L0785_TOTALE").Select
.Fields("DATA_CONT") = Range("A" & r).Value
.Fields("DIP") = Range("B" & r).Value
.Fields("COD_BATCH") = Range("C" & r).Value
.Fields("C_C") = Range("D" & r).Value
.Fields("NOMINATIVO") = Range("E" & r).Value
.Fields("CAUS") = Range("F" & r).Value
.Fields("DARE") = Range("G" & r).Value
.Fields("AVERE") = Range("H" & r).Value
.Fields("VAL") = Range("I" & r).Value
.Fields("SPORT_MIT") = Range("J" & r).Value
.Fields("ANOM") = Range("K" & r).Value
.Fields("DESCR") = Range("L" & r).Value
.Fields("CRO") = Range("M" & r).Value
.Fields("ABI") = Range("N" & r).Value
.Fields("CAB") = Range("O" & r).Value
.Fields("PAG_IMP") = Range("P" & r).Value
.Fields("NR_ASS") = Range("Q" & r).Value
.Fields("MT") = Range("R" & r).Value
.Fields("SERVIZIO") = Range("S" & r).Value
.Fields("NOTE_BOU") = Range("T" & r).Value
.Fields("SPESE") = Range("U" & r).Value
.Fields("DATA_ATT") = Range("V" & r).Value
.Fields("COD") = Range("W" & r).Value
.Fields("NOTA_LIB") = Range("X" & r).Value
.Update ' stores the new record
End With

r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

XL-Dennis
11-10-2004, 04:25 PM
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21201591.html

sal21
11-11-2004, 11:59 AM
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21201591.html
many tks, i have solved....;-)