[After posting this thread i realised the title would be better described by something like : " Upload and update seperate excel sheets into one Access table". But I noticed i cant change this anymore]
Hi all,
With the limited knowledge i have, but i'm learning, i'm starting a little access database project.
The idea is to let multiple users who are using separte excel files with the same data layout upload their data into an access table using an Macro.
The excel files all have the same header and contain the agreements they made with customers which will result in an invoice. The agreements are entered in one row which starts with an unique reference (persons initals and a number).
Below you find a macro which i managed to create so far. It will upload the data in the sheet to a seperate table within the database, after it has deleted all the data in that table.
I want to edit this marco so that it will upload the data of all the seperate excel files into one table in the acces database and update the data if changes have been made.
THe following step is that i can download all the whole table from this access database and add some invoice information which will be updated in the database and in the excel sheets of all the users.
I would be very grateful if anyone could tell me how to continue.
Sub ADOFromExcelToAccess()
' 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
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=D:\database1.accdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "MONPAs", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
strSQL = "delete * from MONPAs"
cn.Execute strSQL
r = 7 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Reference") = Range("A" & r).Value
.Fields("Commitment date") = Range("B" & r).Value
.Fields("Customer") = Range("C" & r).Value
.Fields("Amount excl VAT") = Range("D" & r).Value
.Fields("Kind of budget") = Range("E" & r).Value
.Fields("Description") = Range("F" & r).Value
.Fields("Expected invoice date") = Range("G" & r).Value
.Fields("QTY") = Range("H" & r).Value
.Fields("Old price") = Range("I" & r).Value
.Fields("New price") = Range("J" & r).Value
.Fields("Comments") = Range("K" & r).Value
.Fields("Customer sell to") = Range("L" & r).Value
.Fields("Amount booked") = Range("M" & r).Value
.Fields("Amount left") = Range("N" & r).Value
.Fields("Invoice date") = Range("O" & r).Value
.Fields("Invoice booked") = Range("P" & r).Value
.Fields("Invoice number") = Range("Q" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Bookmarks