Import data from an Excel spreadsheet

I have been asked to develop a program that will open an excel spreadsheet, read the data and save it in a table. I have written the code and it works well on test but I fear that it will be unreliable.

The way I am doing it is using DoCmd.TransferText to transfer the entire spreadsheet into a table that I then step through and decipher. However reading the data accurately is dependent on it being in the table in exactly the same order as the spreadsheet but my concern is that the order will be mucked up (I've used this approach before but this does happen occasionally).

I could launch an instance of Excel but I will then run into problems matching the references to the various versions of Access on the user's machine. This little program will eventually be used by upwards of 300 users whose computers are not consistently configured.

I am after suggestions
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
I generally do this:

1.  Use the fileDialog to select the file to import.  I usually maintain a table of the files that were imported and the number of records in each import.  This allows me to warn the user if the same file has already been uploaded.  The down side is that if someone changes the file name, there is no way to test for this unless the files you are uploading have a column or multiple columns which can be used as a unique index.  If that is the case, you can add that step to the checks in step #5.
2.  Use Excel automation to open the Excel file and determine how many worksheets contain data.  If it is more than one,
I read those worksheet names into a list and display popup a form to allow the user to select the worksheet to link.  Make sure you close the workbook and Exit out of Excel before you proceed.
3.  use the TransferSpreadsheet to LINK the excel spreadsheet to the Access database
4.  Loop through the column headers in the LINKED table and make sure that they match the column headers I expect to see in this particular import.
5.  If there are certain data restrictions (referential integrity issues, magnitude, NULLs, incorrect datatype), then I run a series of queries to check to make sure those criteria have been met.  Those queries, generally write data to an Import Errors table that contains fields like:

FieldName, InvalidValue,  Reason

If there are records in this table at the end of this process, I present the errors to the user in a form, allow them to print out a report, and then delete the linked table and exit the routine.  
6.  If there are no errors, then I run a query which pulls the data from the spreadsheet into the production tables.

However, I'm concerned that you might have over 300 people doing this.  Generally this type of process is handled by a single individual to ensure there is no repetition (don't upload the same file twice).
Gustav BrockCIOCommented:
> 3.  use the TransferSpreadsheet to LINK the excel spreadsheet to the Access database

That's what I use for such cases.
However, I have never tried that with 300 users.

Rob4077Author Commented:
Hi Dale, thanks for the comprehensive summary.

Duplication of data input, multiple worksheets in a workbook and file names are not a problem in this instance.

The problem is that the spreadsheet that gets imported is not in data format, it's in a report format. Some cells have been joined to create a more appealing section header, some data is in columns alongside the related category, some data is below the category heading. However every report is in exactly the same format so what I need to do is start at the top, loop down till I find a record that contains the first section heading, then loop through all the following lines importing the data till I get to the end of that section before moving to the next section etc.

You suggested using TransferSpreadsheet to LINK the excel spreadsheet to Access whereas I have been importing. If I import I run the risk of losing the sequence since there's no usable index but if I can link to it, that should keep the order consistent. How do I link and let MS Access invent field names, as it does with an import? That may be the solution to my problem.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Gustav BrockCIOCommented:
That's this command:

    DoCmd.TransferSpreadsheet acLink, ... etc.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob4077Author Commented:
Got it. Just figured out how to use the TransferSpreadsheet method. As long as it creates its own field names consistently and without issue then that should solve my problem. Thanks for your help Dale, and for your confirmation Gustav
Gustav BrockCIOCommented:
You are welcome!

I could add, that if it was possible for you to apply Named Ranges to the cell ranges that hold the data you need, it would be much easier because you then could link each of these Named Ranges as an individual table.

Dale FyeOwner, Developing Solutions LLCCommented:
In addition to the Named Ranges, if the data is guaranteed to start at a particular row/column combination, you can actually specify the range to link, it is the 2nd to last argument in the TransferSpreadsheet argument list.
Rob4077Author Commented:
Thanks for the extra tips. I will study the spreadsheets to see if I can utilize those ideas. Thanks again to both of you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.