Solved

Excel Import into Access 2007

Posted on 2012-04-05
6
301 Views
Last Modified: 2012-04-15
I need to export a spreadsheet sheet  into a new Access Table. No problem doing so, when I manually run the import, the rows come into the table in the same order as the spreadsheet, which is exactly what I want. When I run this in a Macro using RunSavedImportExport, the import works fine, but the order of the data is different than in the original spreadsheet. This is a problem for me as I use the contents of the data in the first 3 rows to run a series of modules.

How do I keep the same order in the table as I had in the spreadsheet? I am not using any Keys or auto numbering?
0
Comment
Question by:rrudolph
  • 2
  • 2
  • 2
6 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 37813790
This is why I try to create a "Numbering" (SortBy) column in Excel too.

This way even if the data comes in with the wrong order, you can resort the fields (via Indexing or setting the SortBy column as the Primary key) in the Access table correctly.

Perhaps another Expert has something more elegant...
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 37813903
use recordsets, read the excel file, row by row and insert to table as you go.
0
 

Assisted Solution

by:rrudolph
rrudolph earned 0 total points
ID: 37813932
Can you point me to a code snippet that would show this in action:

Spreadsheet Name is:  MasterPrice_Template.xls
Sheet Name is: Export
Access Table is PriceImport

Keep in mind, I never know how many columns or rows the spreadsheet will have

Thank you
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 37813936
upload a copy of the db and excel file
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 37823661
A table, technically, does not have an "Order"
If you need the records in a certain order, then why not simply create query to sort them in that order?

In other words, I am not quite sure why the table needs to import in a certain order...
If the table does not import in your specific order, then make a query to do so...

A query can be used just like a table in most cases...
0
 

Author Closing Comment

by:rrudolph
ID: 37848035
I ended up adding a key field programatically and sorting the record set the way I wanted to read the records. Nobody furnished a silver bullet, but all help was appreciated and did confirm what I already suspected.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

947 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

19 Experts available now in Live!

Get 1:1 Help Now