Transfer data from excel to Access

[djsmarties]

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

 

[DonkeyOte]

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]

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]

thanks,
i prefer to run the whole thing from access, but if its easier in from excel that;s fine too.

 

[DonkeyOte]

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]

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]

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?