HELP A NEWBIE - EXCEL TO ACCESS

[BillT]

Hi Everyone:

I'm new to Access and I need help automating a process to save me a ton of
time.

I'm a math department chair and I have a database of student information
such as marking period grades and exam scores. It's a very simple database
and every year I import all of the 6-12 students as new records with the same
fields, but just a different school year.

Anyway, all of the students have a unique id number in the district.
Teachers send me spreadsheets of data with the students names, id number and
then different columns of assorted data. I now have to update the records in
the main table from these spreadsheets. I have been first importing the data
into a new table from the Excel spreadsheet, and then once the new table is
created I create an update query for each teacher. Is there an easier way? I
have 24 teachers and this takes about 15 minutes or so each. I would really
like to know if there is another way, and also if there is a better way to
gather information from teachers than Excel. They like to add information
from home and so there is no access from home to the database.

Thanks for the time,

Bill

 

[Mark Andrews]

If you can standardize the excel file the teachers use so it has the same
columns you could automate the process to read in all the excel files.
Excel is one of the best file formats to use.

Example:
- you put all the spreadsheets in a directory
- you press a button to read in all the results

If it's taking you 6 hours each time the entire process could be automated
in less time than that (if you can get
consistant columns in the excel spreadsheets.

HTH,

 

[BillT]

That sounds exactly like what I need Mark! So I could send out an Excel sheet
with each teachers classes and the same column headers and they could just
email me back. Could you point me toward a "How To"?

Bill

 

[golfinray]

Check out the transferspreadsheet method and you could automate that where
you wouldn't have to do a thing. Just Google Transferspreadsheet.

 

[Ken Snell]

You'll find various code examples for importing data from EXCEL into ACCESS
here, including importing multiple workbooks / worksheets:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

 

[Mark Andrews]

Save all files to a directory first then
You would need some code that loops thru the files in a directory and then
use the transferspreadsheet method to do the importing for each filename and
then use an append or update query or both to move the data from your import
table into your real table.

Sometimes you might need to cleanup the import table a bit (extra rows with
blanks that you don't want etc...).

With excel you can also use a subset of the columns and it will still work.

It looks like the examples Ken pointed you to would help you make the code.