pulling a field from access into excel when you open the spreadsheet

[tbutcher]

Hi guys

 

i'm looking to try and pull the data from an access database into an excel spreadsheet. I can do this one of two ways preferrably. I have an access database of information about people, contact details etc, but i then want to do a simple register to go along with it. now, i think that there are two ways of doing this that should be quite simple, i just don't know how to do them!

 

first, i thought that i could make it so that when i open the excel spreadsheet, it looks at the database and fills the first column with the names of all the people, much like a lookup does to pull data from 1 sheet to another. the only reason i can think this wouldn't work, is that the database has a password for obvious security reasons.

 

secondly, i thought that i could do it the opposite way round, and when i open the access database, it automatically opens (and updates?) the spreadsheet. the only problem with this is, i need to be able to add new people to the database, and then they immediately appear inside the excel spreadsheet.

 

any ideas how to do this? or is there a better way of doing it?

[srochford]

There's no problem with Excel loading data from an Access database when it opens. On the data tab in Excel just click the "from Access" button, browse to your DB, select the query containing the data you want and job done. Each time you open Excel it will prompt you to see if you want to update the data from the linked source.

 

You could do it from Excel. You could have a form with a button on it, when you click the button it runs an export to Excel.

 

Not sure I understand what you're trying to do. Do you want the Excel sheet to open so that it can be filled in on screen? If so, what happens to the data once its entered - it won't be easy to get it back into the database if that's the idea.

 

If you're going to print out paper forms then an Access report is probably easier (and will be "live" each time its run)

 

If you just want to fill in on screen, you could just use a query in Access to select the correct group of people and then link a form to that - the form can have a mix of labels for fixed data (name etc) and

text boxes where you can enter data.

 

Hope that gives you some ideas to get started!

[tbutcher]

ok excellent that sounds brilliant!

 

basically i am planning to use an access database to store personal data of people attending for contact and medical reasons, and then the excel spreadsheet will be for actually registering them every week.

 

included in my access database is currently a registration bit, but it is very clunky to use when i have about 50+ people to register as quickly as possible.

 

anyway, the database i'm using is the default access 2007 one that does registrations.

 

would it be an option, to speed along the clunky access registration, to use the excel spreadsheet to 1) pull their names into and 2) put the registration details back into for the relevant date (probably just a Y or N for registration)