Linking Numerous excel sheets into ONE access table

[Sabrina224]

I have 7 users entering data in their own excel spreadsheets on their own computers (which are networked). Each spreadsheet has identical columns. Without having to physically import the data from each spreadsheet one by one into a SINGLE table is there a way to link them all to the same access table? I've tried linking, but as far as I can tell, only ONE worksheet can be linked to a given table. Or am I going about this all wrong? Ultimately, what I hope to accomplish, is for all their data to be placed in a single access table upon request. (I am working on a db where they can just enter the data directly into access, but the formulas are quite complicated so it will take me a while to get to that point.) Or, perhaps there is code for importing where I can specify the path of each form and just run the function? Can someone please head me in the right direction -- I can't see the trees through the forest anymore. Any and all help appreciated!

 

[Ecniv]

Hi,

I read that you are working on getting the forms set up for them all to enter into the same table. As to the formulaes, if you can, forget them and just get the data they are entering into a form for them to use.

The formulaes you can come back to at a later date, and get access to loop through stored records to process them.

Thats if you are allowed to do that method. At least all the data the users enter will go straight into a table ready for processing etc later. It also stops them holding separate spreadsheets, and once you have that form in you can stop them using the spreadsheets, then import all the typed in data for those too.

Of course, this is just for the ideal situation and I bet the company want it all or nothing.

Another possiblity is to use automation and write an import sub/function which opens their excel files and imports in the data. Then you have to figure whether its already been imported... hmmm.

Just some ideas, sorry if they aren't helpful, but you are the developer so you can pitch it to your boss etc for the best route to take.

Vince

 

[Sabrina224]

Thanks Vince -- all help is appreciated!!! and you're right, they want it all, or nothing. I figured I could at least get the BE setup and at least use queries and reports on existing data (making my life a little easier) until I got the rest up and running. I didn't think it was possible, but you never know 'till you try!

thanks!