Solved

Import partial Excel spreadsheet into Access empty table

Posted on 2007-11-13
7
1,645 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
7 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 118 total points
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 5

Assisted Solution

by:sinjin
sinjin earned 116 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Forced accept.

Computer101
EE Admin
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now