Results 1 to 2 of 2
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    controll duplicat during import (2000 sr.1)

    This script import the records of the table into excel sheet.
    I would want to controll during the import a record already existis into sheet... The unique id is column S of the sheet and field SERVIZIO of the table...
    I have make this macro but my problem is to go on the nex rocord of the mdb...

    example:
    if the record of mdb existis in the sheet, go to the next record of mdb...
    if not existis inserting in the bottom of the sheet,ecc...

    My script read always the first record of table...I do not know to increase the parameter "OggettoRecordset.MoveNext" for nex record ...

    Note: the part of my code is comment...

    the db is here: http://www.gssitaly.com/prova.zip
    <code>
    Sub IMPORTA()
    Workbooks("IMPORTA_MDB_ACCESS.XLS").Activate
    Set ELENCO = Worksheets("FINAL")
    CONT = FirstFree("FINAL", "A", 6)
    Dim NomeDB As String
    NomeDB = "D:PROVAPROVA.MDB"

    Dim StringaDiConnessione
    StringaDiConnessione = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NomeDB & ";'"
    Dim OggettoConnessione As Object, OggettoRecordset As Object
    Set OggettoConnessione = CreateObject("ADODB.Connection")
    OggettoConnessione.Open StringaDiConnessione
    Set OggettoRecordset = CreateObject("ADODB.Recordset")
    Set OggettoRecordset = OggettoConnessione.Execute("SELECT * from TOTALE")

    Do While Not OggettoRecordset.EOF

    'ID = OggettoRecordset("SERVIZIO")
    'Set Found_ID = Sheets("FINAL").Columns("S:S").Find(ID, lookat:=xlWhole)
    'If Found_ID Is Nothing Then
    ELENCO.Range("A" & Trim(Str(CONT))).Value = OggettoRecordset("DATA_CONT")
    ELENCO.Range("B" & Trim(Str(CONT))).Value = OggettoRecordset("DIP")
    ELENCO.Range("C" & Trim(Str(CONT))).Value = OggettoRecordset("COD_BATCH")
    ELENCO.Range("D" & Trim(Str(CONT))).Value = OggettoRecordset("C_C")
    ELENCO.Range("E" & Trim(Str(CONT))).Value = OggettoRecordset("NOMINATIVO")
    ELENCO.Range("F" & Trim(Str(CONT))).Value = OggettoRecordset("CAUS")
    ELENCO.Range("G" & Trim(Str(CONT))).Value = OggettoRecordset("DARE")
    ELENCO.Range("H" & Trim(Str(CONT))).Value = OggettoRecordset("AVERE")
    ELENCO.Range("I" & Trim(Str(CONT))).Value = OggettoRecordset("VAL")
    ELENCO.Range("J" & Trim(Str(CONT))).Value = OggettoRecordset("SPORT_MIT")
    ELENCO.Range("K" & Trim(Str(CONT))).Value = OggettoRecordset("ANOM")
    ELENCO.Range("L" & Trim(Str(CONT))).Value = OggettoRecordset("DESCR")
    ELENCO.Range("M" & Trim(Str(CONT))).Value = OggettoRecordset("CRO")
    ELENCO.Range("N" & Trim(Str(CONT))).Value = OggettoRecordset("ABI")
    ELENCO.Range("O" & Trim(Str(CONT))).Value = OggettoRecordset("CAB")
    ELENCO.Range("P" & Trim(Str(CONT))).Value = OggettoRecordset("PAG_IMP")
    ELENCO.Range("Q" & Trim(Str(CONT))).Value = OggettoRecordset("NR_ASS")
    ELENCO.Range("R" & Trim(Str(CONT))).Value = OggettoRecordset("MT")
    ELENCO.Range("S" & Trim(Str(CONT))).Value = OggettoRecordset("SERVIZIO")
    ELENCO.Range("T" & Trim(Str(CONT))).Value = OggettoRecordset("NOTE_BOU")
    ELENCO.Range("U" & Trim(Str(CONT))).Value = OggettoRecordset("SPESE")
    ELENCO.Range("V" & Trim(Str(CONT))).Value = OggettoRecordset("DATA_ATT")
    ELENCO.Range("W" & Trim(Str(CONT))).Value = OggettoRecordset("COD")
    ELENCO.Range("X" & Trim(Str(CONT))).Value = OggettoRecordset("NOTA_LIB")
    CONT = CONT + 1
    OggettoRecordset.MoveNext

    'End If

    Loop

    Range("A7").Select

    OggettoRecordset.Close
    Set OggettoRecordset = Nothing
    OggettoConnessione.Close
    Set OggettoConnessione = Nothing
    End Sub
    Public Function FirstFree(Tabella, Colonna, RigaIniziale)
    CONT = RigaIniziale
    lTest = False
    Set Check = Worksheets(Tabella).Range(Colonna + CStr(CONT))
    Do While IsEmpty(Check) <> True
    Set Check = Worksheets(Tabella).Range(Colonna + CStr(CONT))
    If IsEmpty(Check) <> True Then
    CONT = CONT + 25
    Else
    x = CONT
    Do Until lTest = True
    Set oTest = Worksheets(Tabella).Range(Colonna + CStr(x))
    If IsEmpty(oTest) = True Then
    x = x - 1
    Else
    FirstFree = x + 1
    lTest = True
    End If
    Loop
    End If
    Loop
    End Function
    </code>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: controll duplicat during import (2000 sr.1)

    You also posted this in the Excel forum. Please do NOT post the same question twice. This thread is locked. Please post all answers to <post#=426386>post 426386</post#>.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •