Fog Creek Software
Discussion Board

Import Data from Excel to MS Access(VBA)


Can any one help me getting data from Excel to Access. I want to put data in two different tables which is in random form in MS Excel. I mean data is not on once place (different cells) but it on one sheet.

Thank you

Khurram Shahzad
Thursday, July 1, 2004

Easiest way is to copy the excel range, switch to Access right click on the Table window and select paste.
Indicate whether the first row holds field names.
Check to see if you have any errors.  Where you do tidy the data up in Excel and re-try.
You can attempt to import from Access but thats a little fiddly.
For slightly more in depth guidance you might want to try  and search for ms access.

All the best

Thursday, July 1, 2004

I usually export the table to excel, then copy/paste under the columns and then re-import into access...


Thursday, July 1, 2004

You best for these types of questions is the access newsgroups. They are very active, and questions get answered real fast.

To do the transfer, you can use transferSpreadSheet.

DoCmd.TransferSpreadsheet acImport, , "tblImport", _
          "C:\Documents and Settings\Albert\Desktop\palmsheets.xls", True, "A1:o10"

The above would input the first 10 rows from a spreadsheet, and the “true” setting assumes field names.

You can see how it is legal to use a range. Check out the transferSpreadsheet command in the help.

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Thursday, July 1, 2004

*  Recent Topics

*  Fog Creek Home