Import apend data

[wjburke2]

Hey all, I was wonder if it was possible to import data from a excel spread and append the record to a existing table using VBA. I was looking into a command 'DoCmd.RunSavedImportExport but am not sure if this is the best way. I also use a command transferSpreadsheet in my macro. I believe the transfer spreadsheet wipes out the table and rebuild it from the input every time.

 

[Gunilla]

Hi
this is a code I use to export data from Excel to Accesss. I am using Office 2003.

Sub FromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
Dim db As String
Dim cnt As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim stTable As String

'in case you don't want the total Excel sheet to be exported to
'Access use an inputbox to state the number of the row that is the
'start point for [B]r[/B]. If you always start from the same row number just
'use that number instead of the Inputbox.
r = Inputbox("From what row do you want to start the insert?")

'Path to database [B]NOTE [/B]database and worksheet should be in the same folder
db = ThisWorkbook.Path & "\" & "yourdatabasename.mdb"
' open the database
   Set cnt = New ADODB.Connection
   Set rs = New ADODB.Recordset
stTable = ("Excel") 'Name on table in Access


cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & db & ";"
rs.Open stTable, cnt, adOpenForwardOnly, adLockOptimistic, -1

' get all records in a table

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("ID code") = Range("A" & r).Value
.Fields("ID Name") = Range("B" & r).Value
.Fields("Days") = Range("C" & r).Value
.Fields("Last test") = Range("D" & r).Value
.Update ' stores the new record in Access table
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cnt.Close
Set cnt = Nothing
End Sub

After some correction to fit your needs, you paste the code into an module in Excel and run the macro when you are ready to do the export.
I hope it will help you.
Gunilla

 

[wjburke2]

Thanks Gunilla, This will be harder than I expected but would let me validate the data field by field as I import it.

 

[Bundu86]

Hey, this coding seems to be just what I need.... apart from that I need to do it from Access! I've been going insane trying to find the coding to do this, but to be fair its been the end of a long day so that might be

I will be importing many spreadhsheets each month from Excel spreadsheets into an exisiting Access table. Unfortunately these spreadsheets tend to have the columns in a different order to my exisiting table in Access and with different header names.

What I am looking for is how to specifiy each column and then tell it which column to append to.

An example would be as follows:

In Access I would have a table with the following headings in the following order:

Name, Surname, Address, Country, Value.

For example lets say one of the spreadsheets was laid out as follows:

Address, Cost, Name, Surname, Country.

- Cost would go to Value

If you need any further information please feel free to ask. Also I am trying to learn the code as well so I can adapt it further (hence I have not discussed other parts of this project.

Hopefully it all makes sense!