Transfer data from excel to Access

djsmarties

Board Regular
Joined
Sep 10, 2002
Messages
95
Hi, i want to transfer data from excel to different tables in access. i tried the template wizard, but it only allows me to transfer one row at a time. i would need to import several rows. also, this would have to be done automatically to some extend, macro or vba maybe.
thanks for your suggestions
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
as usual with these it's important to know as to whether you want to run this from Access or from Excel (the Macro).
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Quick example

Dim obj1 As AccessObject
Dim strPath As String
Dim strDBName As String

Set appaccess1 = New Access.Application
strPath = "R:currentFinanceABT"
strFile = "Process.mdb"
strDBName = strPath & strFile
appaccess1.OpenCurrentDatabase strDBName

DoCmd.TransferSpreadsheet acImport, 8, "DEPT_FILTERED_DATA", "R:currentFinanceABTProcess2.xls", True, "A1:L35000"

Where DEPT_FILTERED_DATA = Access Table
The next bit is obviously the Excel file and then the range is the area of copied data to be taken from excel and imported to Access Table.

Luke
 

djsmarties

Board Regular
Joined
Sep 10, 2002
Messages
95
thanks,
i prefer to run the whole thing from access, but if its easier in from excel that;s fine too.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

Well if you want to code in Access - why not use the

Do.Cmd TransferSpreadsheet etc...

That's all Access code just run from an Excel macro.

Filtering the data in the sense that you only import the rows you want to shouldn't be too difficult - you could set up a query in access to do this and post the final data to a separate table, or you could it in Excel prior to importing.
 

klb

Well-known Member
Joined
Apr 3, 2002
Messages
821
Another way is to give your data in excel a range name. Make sure that the first row of the range contains labels that can be used as column headings.

From Access, import the named range into either a new table or an existing table.
 

djsmarties

Board Regular
Joined
Sep 10, 2002
Messages
95
great, the do.cmd spreadsheet works fine.

i can still use range names instead of the direct range, right?
if i want to import from the same file, but different worksheet, is it okay just to use range names?
 

Forum statistics

Threads
1,147,455
Messages
5,741,222
Members
423,649
Latest member
steel1968

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
Top