Import Access Table Into Excel

sal21 · Nov 7th 2004

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:

Code

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

Display More

lasw10 · Nov 7th 2004

you can use ADO in reverse...

you would simply need to first build a NOT IN Clause to exclude the values already present in your database

then run some sql

eg assuming your data in XL is on sheet1 from row 2 onwards...and say the field you will use in your clause is in cell 1 and called "ID"... and you want to put the new data from your .mdb into sheet2

a very simple example would be as follows (note this is actually MYSQL Sql syntax... I don't use MS Access very much so it may need tweaking)

Code

Sub Marine()


Dim c as adodb.connection
Dim r as adodb.recordset
Set c = new adodb.connection
c.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\GCD01F4500\DATI\PUBBLIC


Dim s1, s2, notinstr as string
Dim rw as integer


s1 = "sheet1"
s2 = "sheet2"
notinstr = ""
rw = 2


Sheets(s1).Select


Do Until Cells(rw,1) = ""
     notinstr = notinstr & ", "
rw = rw + 1
Loop
notinstr = left(notinstr,len(notinstr)-2)


sq = "Select * from yourtable where ID not in (" & notinstr & ")"
set r = c.execute(sq)
If r.eof = false then
     Sheets(s2).Select
     Cells(2,1).CopyFromRecordset r
     r.close
End If
c.close


End Sub

Display More

Import Access Table Into Excel

Participate now!

Share