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

    compare and delete line not finded... (2000 sr 1)

    Note: in VBA for excel
    I have a sheet and this MDB with a table.
    My prob:
    Compare or find and delete, the line into the table of MDB.
    Example:
    - the line into sheet is the same line existis into the table of MDB, OK!
    - the line into the table not are present into the sheet delete the line into MDB table not finded.
    In effect alling the line from the sheet.
    The unique ID is filed SERVIZIO and in the column S of the sheet are the same (compare the line with this ID)
    In the example into sheet are present 11 line and in the mdb table cdi_50, are present 22 line
    delete the 11 line into MDB table because not present in the sheet...
    The example ais here:
    http://www.gssitaly.com/test.zip

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

    Re: compare and delete line not finded... (2000 sr 1)

    If you want us to help you, please cut down the files in size, put them in a zip file (smaller than 100 KB) and attach the zip file. (See <post#=425456>post 425456</post#> where I got the spreadsheet and database into a 41 KB zip file)

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: compare and delete line not finded... (2000 sr 1)

    i am sorry!
    Here is....

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

    Re: compare and delete line not finded... (2000 sr 1)

    You can insert this macro into a module in the Excel workbook:Sub DeleteMissing()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strServizio As String
    ' *** Substitute the correct path! ***
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=F:Exceltest.mdb;" & _
    "Persist Security Info=False"
    rst.Open "CDI_50", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    Do While Not rst.EOF
    strServizio = rst!SERVIZIO
    If Worksheets("L0785_CDI_50").Range("S:S").Find(strSe rvizio) Is Nothing Then
    rst.Delete
    End If
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
    End Sub

    Notes:
    - You must set a reference to an Microsoft ActiveX Data Objects 2.n Library. You probably have such a reference already in your working spreadsheet.
    - Don't forget to change the path of the database.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: compare and delete line not finded... (2000 sr 1)

    <big>ULTRA MEGA STAR CODE, SURE YOU ARE A WIZARD.</big>

Posting Permissions

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