Solved

Import partial Excel spreadsheet into Access empty table

Posted on 2007-11-13
7
1,677 Views
Last Modified: 2008-03-26
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
Comment
Question by:JoeBoyd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20276191
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 118 total points
ID: 20276206
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
 
LVL 5

Expert Comment

by:sinjin
ID: 20276523
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
Technology Partners: 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!

 
LVL 5

Assisted Solution

by:sinjin
sinjin earned 116 total points
ID: 20276558
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
 
LVL 4

Assisted Solution

by:MrXmas
MrXmas earned 116 total points
ID: 20277483
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
 
LVL 1

Expert Comment

by:Computer101
ID: 21216444
Forced accept.

Computer101
EE Admin
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question