Office Software Thread, pulling a field from access into excel when you open the spreadsheet in Technical; Hi guys
i'm looking to try and pull the data from an access database into an excel spreadsheet. I can ...
4th February 2010, 08:43 PM #1
pulling a field from access into excel when you open the spreadsheet
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?
IDG Tech News
4th February 2010, 11:26 PM #2
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!
5th February 2010, 09:47 AM #3
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)
By DaveP in forum Downloads
Last Post: 23rd December 2009, 12:04 PM
By djones in forum Scripts
Last Post: 4th August 2009, 10:51 AM
By Pyroman in forum How do you do....it?
Last Post: 2nd May 2008, 04:19 PM
By Uraken in forum Web Development
Last Post: 21st November 2007, 05:23 PM
By danIT in forum Windows
Last Post: 3rd July 2007, 02:28 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)