• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1704
  • Last Modified:

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.
0
JoeBoyd
Asked:
JoeBoyd
3 Solutions
 
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
 
Rey Obrero (Capricorn1)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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sinjinCommented:
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
 
MrXmasCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now