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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
/gustav
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.
/gustav
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.
ASKER
Thanks for the extra tips. I will study the spreadsheets to see if I can utilize those ideas. Thanks again to both of you
That's what I use for such cases.
However, I have never tried that with 300 users.
/gustav