+ Post New Thread
Results 1 to 3 of 3
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 ...
  1. #1

    Join Date
    Sep 2009
    Posts
    130
    Thank Post
    6
    Thanked 12 Times in 9 Posts
    Rep Power
    36

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

    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?

  2. #2

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,157
    Thank Post
    116
    Thanked 529 Times in 452 Posts
    Blog Entries
    2
    Rep Power
    124
    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!

  3. #3

    Join Date
    Sep 2009
    Posts
    130
    Thank Post
    6
    Thanked 12 Times in 9 Posts
    Rep Power
    36
    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)

SHARE:
+ Post New Thread

Similar Threads

  1. Word Search - Excel Spreadsheet
    By DaveP in forum Downloads
    Replies: 0
    Last Post: 23rd December 2009, 11:04 AM
  2. excel date field to general numbers
    By djones in forum Scripts
    Replies: 3
    Last Post: 4th August 2009, 09:51 AM
  3. Microsoft Access Help! Date Field
    By Pyroman in forum How do you do....it?
    Replies: 1
    Last Post: 2nd May 2008, 03:19 PM
  4. excel spreadsheet editable online
    By Uraken in forum Web Development
    Replies: 4
    Last Post: 21st November 2007, 04:23 PM
  5. Excel - Spreadsheet Manipulation
    By danIT in forum Windows
    Replies: 1
    Last Post: 3rd July 2007, 01:28 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •