Firstly, I would like to thank all the people who respond to posts - I have managed to get a hell of a lot done thanks to the kindness of these people, so thank you.

Right - here goes with my latest problem.

I have an XL sheet that is set up to be used as a rating engine for a colleague. This sheet will be used for all his clients. Data gets entered into the original sheet (same sheet everytime) and various code will produce graphs and numbers. My colleague will then make a decision based on this data and save the file with the client name_2009. (This is where I come in and make things difficult!) What I would like to do is when the first stage has been completed, get the data that is stored in a seperate tab on this sheet and import it into an Access db table. The table will hold all the data for all the clients. So when the next clients data gets entered into the original sheet and gets processed, the new data appends to the current table.

Firstly, is this possible please? And secondly, if it is, how would i go about it? My sql/vba skills are limited!

Thanks and Regards
Dingo

Probably...

Link from Access to the tab in the Excel sheet (use the Get External Data from the file menu) then append the data to your table in Access.

Thanks for the response neileg.

Probably didn't explain my predicament as well as I thought!

Ideally I do not want to have my colleague using both systems at the same time. The way I envisaged it working is that my colleague would enter the data into the XL rater, hit the command button to upload the data into the Access db, then name and save the sheet where ever he wants. He can do this numerous times for various clients and each time he hits the command button, the data gets appended in the table.

I don't think your suggestion will allow this.

In my head this is quite straighforward, but the more I look into it, the more it seems as if I will need to re-think the way in which this is set up.

Appreciate your comments/feedback.

Regards
Dingo

You can't really use Excel to populate an Access table. So if you want to stay in Excel, the best you can do is populate another Excel table that is linked to your Access app so the data is available when you open it.

Alternatively, you could use Access to do the data capture and export this to your Excel sheet.

I think you'll need to compromise somewhere.

Thanks for your advice.

I will have a re-think and suggest the rater document gets built in Access!

Regards

There is no reason why you cannot get Excel to populate a table in Access I do it at least 5 times a day every day. First create the code in your personal.xls then create a custom menu item that fires of the Excel Macro simple as that.

Because the code is stored in your personal.xls it does not matter what spreadsheet is opened as long as the data is in the same sheet.

David

There is no reason why you cannot get Excel to populate a table in Access I do it at least 5 times a day every day. First create the code in your personal.xls then create a custom menu item that fires of the Excel Macro simple as that.

Because the code is stored in your personal.xls it does not matter what spreadsheet is opened as long as the data is in the same sheet.

DavidMy apologies, then. I freely admit to being wrong!