Solved

Import partial Excel spreadsheet into Access empty table

Posted on 2007-11-13
7
1,689 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

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…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

617 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