transfer from Excel to Access

Andrew XJ

Board Regular
Joined
Feb 21, 2002
Messages
77
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
Thanks a lot, Damon!!
I believe this is extremely useful for my project!!! I will try it in my project.
best regards
Xinjun
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top