Import partial Excel spreadsheet into Access empty table

I have a table in access with 15 fields.  The table is empt I- no data yet.  I have a spreadsheet that has 22 columns, 10 of the columns match 10 of the 15 columns in the Access table exactly.  I want to import just the data from just the 10 columns of the spreadsheet.   What is the best way to do this and how.  Can I just import it directly into the Access table or do I have let the import create a new table and then append this new table to the other table where I really want the data.  I guess what I'm saying is what is the best way to handle importing a spreadsheet table where only part of the spreadsheet matches part of the table exactly.  
Thank you,
Joe B.
JoeBoydAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
oops,
otherwise you have to import the whole sheet to a temp table and just append the data to the Table using a query with the selected fields.

or you can use vba to select the excel columns...more codes.
0
 
Rey Obrero (Capricorn1)Commented:
if the first 10 columns of the spreadsheet are the ones you want to import

you can use

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "TestTable", "C:\T.XLS", True, "Sheet1!A:J"
0
 
sinjinCommented:
I would go to excel and create a named range then import the named range.  Here is how you do it:
Open excel and select all the cells, row, columns, etc that you wish to import.
Go to Insert, Name, Define.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
sinjinConnect With a Mentor Commented:
Oooops... incomplete answer above... mistakenly pressed tab then enter /headsmack
Insert, Name, Define.
Give the range a name making sure it has no spaces and does not begin with a non-alpha character.  Also, named ranges must be uniquely named in the excel workbook.  that is you can't have the same named range in the same workbook.

Go to access and import the table.... if you use the wizard it will give you the option to import a named range.  The same options exist in the VBA code.... using DoCmd.TransferSpreadsheet.

luck,
 sinjin
0
 
MrXmasConnect With a Mentor Commented:
JoeBoyd,

If this is a one time only affair then you can do this:

Arrange the columns on the spreadsheet and on the table view in the same exact order (this is easiest in Access because you can re-arrange columns at will).  Then copy the data from Excel (without the header row), switch back over to Access, put your cursor in the first box and Select Edit - Paste Append.

--Jim Christmas
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.