retrieve data from excel and bring into access

[dnr]

Hello!

Our manufacturing group saves a spreadsheet every production lot to collect
thickness data. The spreadsheet has three tabs with one of the tabs having
the data average, minimum, and maximum. There are hundreds of these
spreadsheets and I need to get the average, minimun and maximum of all of
them. I have created a table in access that has four columns: lot number,
average, minimum and maximum. Each spreadsheet is saved by its lot number.
Is there a code that will go thru a file folder determine if a record exists
in the database with the lot number, if not open the file retrieve the data
enter it in the table close the file and go to the next one. Please help!

Thanks

 

[Jeanette Cunningham]

dnr,
based on your description of the challenge, the easiest way is probably to
import the data from each spreadsheet.

Klatuu posted some code here this morning that will loop through all the
spreadsheets in a folder and import the data.

If you want to import to the table you created, make sure that the column
headings in excel match exactly with the table headings in access.

If you have problems getting it to import to your table, it will be easier
to import to a new table.

If you import to a new table, then the next step is to run an append query
to move the data from the new table to your existing table.

To prevent getting duplicate records for the same lot number, you can create
a unique index on the field for lot number. This will make the append query
append only the new records into the
database.

The link for Klatuu's code is
http://groups.google.com/group/micr...aldata/browse_thread/thread/889abf3d308a1e38#


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Michael Conroy]

dnr,
I ran into a situation like this and it caused me to analyse why I had
hundreds of spreadsheets. Could you make one file with the four tabs and put
all the past information and any new ones into that file. You could then link
Access to the spreadsheet and run reports. In other words, instead of using a
new file as your primary key, put the lot number in each tab along with the
measurements, and keep it in one big file. If you are familiar with DSum,
DCount in Excel you might be able to generate the reports without Access
(heaven forbid). If you are building the database to get rid of the
spreadsheets and have the information entered directly into Access, great,
however, my suggestion would be to copy and past all that data into four
separate tabs, and do just four imports. This gives you a chance to clean the
data before you import. Good Luck.