database to store data entered from a spreadsheet?

[Dan B]

Hi,
I'm looking for a little direction....
I have about 6 different spreadsheets that people enter data into. The
layout of the spreadsheets are identical. They each have one worksheet per
month on the year and then a summary page. Survey results and statistical
information are entered into these spreadsheets by different people.

What I would like to do is to able to summarize each monthly worksheet and
each summary sheet from all six spreadsheets (separate files). I know I
could link the six sheets to a new summary sheet, but that would be
cumbersome due to the number of items being entered in.

Is there a better way....like having each of those sheets dump into a
database? What is the best way to accomplish this and where is a good place
to read up on how to do it?

Thanks!

Dan

 

[Ron de Bruin]

Hi Dan

You can Use Excel to copy all data in one worksheet

Try this
http://www.rondebruin.nl/copy3.htm

 

[Dan B]

I tried this and got a Run-time error '9': Subscript out of range error. I
changed this line:

Set sourceRange = mybook.Worksheets("test").Range("A1:j51")

and the C:\data to the correct path on my network.
I guess that caused the error.

Will this summarize all of my sheets or just copy them?

Is there a way to have the data go to an access database?

Thanks.

 

[Ron de Bruin]

Hi Dan

Have every workbook a sheet with that name?
Test it first with a folder on your own system

Will this summarize all of my sheets or just copy them? Copy

Is there a way to have the data go to an access database?

Look here
http://www.erlandsendata.no/english/index.php?t=envbadac

 

[Rob]

Hi Dan,

You can very easily import excel data in Access if you have it.
Open Access, open a new db, right click on an empty area, choose Import,
change the 'Files of Type' to xls and path to your excel files. The wizard
will walk you through the rest.
Good place to start.

OK
R