transfer from Excel to Access

[Andrew XJ]

Could you help me to solve a problem of transferring Excel cell data to Access. I need to create a Macro in an Excel file so that when user click a button or press certain hot key, values of some cells in some sheets will be transferred to some Access tables.
I don't mean to export list-formatted Excel to Access table.

[Damon Ostrander]

Hi XJ,

Perhaps this will help. This is just a little demonstration code that takes a few values from worksheet cells and puts them directly into fields named FirstName and LastName in an Access Table named "Employees".

Sub ToAccess()

Dim axsApp As Object
'Assume Access already open with desired database
Set axsApp = GetObject(, "Access.Application")

Set cdb = axsApp.currentdb
Set rst = cdb.openrecordset("Employees")

Dim iRow As Long 'Excel worksheet row number

'Loop thru first 8 rows on active worksheet and put data from
'first two columns into Access employees table

For iRow = 1 To 8

With rst
.addnew
' read the names from Excel into Access table
!LastName = Cells(iRow, 2)
!FirstName = Cells(iRow, 1)
.Update
End With

Next iRow

End Sub

[Andrew XJ]

Thanks a lot, Damon!!
I believe this is extremely useful for my project!!! I will try it in my project.
best regards
Xinjun

[dpmicka]

Any way to modify the code listed above by Damon (it works great, by the way) such that the Access db doesn't have to be open at the time the Excel macro is executed??? Just wondering...