Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate Record....
I export a sheet of excel into table (PAGATI) with this cript i have make a controll on a duplicate in column M of excel and in field CRO of access table. If i re-import the macro not consider the duplicate... Is corect thi sscript... Please test for me. Naturally if you have another way to controll duplicate during the import is welcome (set your path) : Sub ADO_PAGATI() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long Dim rsFind As ADODB.Recordset ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\GCD01F4500\DATI\PUBBLICA\BOUASS\PROVA.MDB ;" ' "Data Source=D:\PROVA\PROVA.MDB;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "PAGATI", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 7 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A If Not AlreadyExists(rs, "CRO", Range("M" & r).Text) Then rs.AddNew ' create a new record 'End If With rs 'add values to each field in the record Sheets("L0785_PAGATI").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 Update ' stores the new record End With End If r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub Public Function AlreadyExists(rstTemp As ADODB.Recordset, _ strField As String, strFilter As String) As Boolean ' Set a filter on the specified Recordset object rstTemp.Filter = strField & " = '" & strFilter & "'" If rstTemp.RecordCount 0 Then AlreadyExists = True rstTemp.Filter = "" End Function +-------------------------------------------------------------------+ |Filename: PROVA.zip | |Download: http://www.excelforum.com/attachment.php?postid=2769 | +-------------------------------------------------------------------+ -- sal21 ------------------------------------------------------------------------ sal21's Profile: http://www.excelforum.com/member.php...fo&userid=2040 View this thread: http://www.excelforum.com/showthread...hreadid=276547 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Duplicate Record....
sal21 wrote ...
if you have another way to controll duplicate during the import is welcome The usual way to identify duplicates between two tables is to create an OUTER JOIN on a key and test for null key values in the other table. I've seen a few of your posts now and the details seem to change each time so I've made the following assumptions: - the connection is to you database - the database table is named TOTALE - the Excel workbook is saved as C:\MyWorkbook.xls - the Excel worksheet is named L0785_TOTALE - the column headers are in row 6 and the data starts on the next row - I've ignored column X because it has no column header and the cells contain formulas - the columns in the database and worksheet are the same - the key is SERVIZIO. The following selects rows present in the database but missing from the worksheet: SELECT T1.[DATA CONT#], T1.DIP, T1.COD, T1.[C/C], T1.NOMINATIVO, T1.[CAUS#], T1.[IMP# DARE], T1.[IMP# AVERE], T1.[VAL#], T1.[SPORT# MITT#], T1.[ANOM#], T1.[DESCRIZIONE OPERAZIONE], T1.[INDICE (C#R#O#)], T1.ABI, T1.CAB, T1.[PAG#/IMP#], T1.[NR# ASS#], T1.MT, T1.SERVIZIO, T1.[NOTE B#O#U#], T1.SPESE, T1.[DATA ATTIVITA'], T1.COD1 FROM TOTALE AS T1 LEFT JOIN [Excel 8.0;HDR=YES;Database=C:\MyWorkbook.xls;].[L0785_TOTALE$A6:W65535] AS T2 ON T1.SERVIZIO = T2.SERVIZIO WHERE T2.SERVIZIO IS NULL ; A couple of further points: Your column names are not very database friendly: look at how they appear in the above query. Consider renaming them using just alphanumeric and underscore characters i.e. no spaces, periods, slashes, etc. Attempting to change your Excel data using SQL (UPDATE or INSERT INTO) will cause problems because some of your columns are hidden. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
On Duplicate, update record | Excel Discussion (Misc queries) | |||
How do I remove duplicate in a column but keep the record of the f | New Users to Excel | |||
Finding Duplicate Record in more than one column | Excel Discussion (Misc queries) | |||
Need to color 1 record of each set of Duplicate Records | Excel Worksheet Functions | |||
exel macro to eliminate duplicate record | Excel Programming |