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

    import access table into excel (2000 sr.1)

    Edited by HansV - Script is NOT a good font for code. Pleas do not use it.

    Hi, all
    I impoto a sheet value into access table (with the controll of duplicate column S in the sheet and field SERVIZIO in the table)
    NOTE:THE COLUMN s IN THE SHEET IS HIDE)
    All ok!
    My actual problem is to have the reverse mode: Access table in the sheet of excel (add up the new records not found in sheet)
    there is the MDB file:

    http:/www.gssitaly.com/prova.zip

    This is the code to import sheet into access:
    <pre>Sub ADO_TOTALE()
    ' 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=GCD01F4500DATIPUBBLICABOUASSPROVA.MDB;"
    ' "Data Source=D:PROVAPROVA.MDB;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "TOTALE", 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, "SERVIZIO", Range("S" & r).Text) Then
    rs.AddNew ' create a new record
    'End If
    With rs
    '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
    End If
    r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    End Sub</pre>


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

    Re: import access table into excel (2000 sr.1)

    Why do you want to synchronize the data both ways? This will be very complicated, especially since you mentioned in the Excel forum that you want to give individual copies of the spreadsheet to different users.

Posting Permissions

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