Active TopicsActive Topics  Display List of Forum MembersMemberlist  Search The ForumSearch  HelpHelp
  RegisterRegister  LoginLogin
Please consider donating $1 per sample downloaded, find out why here. If downloading multiple samples, please donate once for all of them.
Amazon Honor System  Click Here
to Pay Learn More
Access 97, 2000, 2002 (XP) (Forum Locked Forum Locked)
 RAL Forums : Access 97, 2000, 2002 (XP)
Subject Topic: import access into excel Post ReplyPost New Topic
Author
Message << Prev Topic | Next Topic >>
sal21
Newbie
Newbie


Joined: 08 November 2004
Posts: 1
Posted: 08 November 2004 at 7:20am | IP Logged Quote sal21

Hi, all
I import 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

and the sheet:

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

This is the code to import sheet into access:

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=\\GCD01F4500\DATI\PUBBLICA\BOUASS\PROVA.MDB;"
     ' "Data Source=D:\PROVA\PROVA.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

 

Back to Top View sal21's Profile Search for other posts by sal21 Send Private Message Add to Buddy List
 

Sorry, you can NOT post a reply.
This forum has been locked by a forum administrator.

  Post ReplyPost New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

Powered by Web Wiz Forums version 7.01
Copyright ©2001-2003 Web Wiz Guide