Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Marietta, Georgia, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Excel Template with Access Database (Excel/Access 2003)

    Is it possible to link a template set up in Excel to pull updated information from a database in Access?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Excel Template with Access Database (Excel/Access 2003)

    You can insert data from an Access table or query into an Excel workbook using Data | Import External Data | New Database Query...
    You can update the data manually by selecting Data | Refresh Data or by clicking the Refresh Data button on the External Data toolbar.
    You can also specify that you want the data to be updated automatically every n minutes, and/or each time the workbook is opened. See screenshot below.
    Attached Images Attached Images
    • File Type: gif x.gif (32.6 KB, 3 views)

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Location
    Marietta, Georgia, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Excel Template with Access Database (Excel/Access 2003)

    Thanks for your reply! Would I be able to recreate the template for the number of sheets required to obtain all the data, or would I need to create multiple pages for it to drop in to?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Excel Template with Access Database (Excel/Access 2003)

    An Excel worksheet has 65,536 rows. The linked data table will automatically expand (or contract) when it is updated, up to this maximum. You can specify that the first row will be printed on each page, in the Sheet tab of File | Page Setup. Excel will take care of the rest.

    If your Access table or query contains more then 65,535 records, you will have to rethink your setup.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Excel Template with Access Database (Excel/Access 2003)

    Hi Hans,

    I am trying to insert data from an Access query into Excel using Date / Import External Data / New Database Query, but i keep getting an error message: "the datasource contains no visible tables". Can you help? (ps. i've tried this on anohter database and it works well)

    thanks, Van

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Excel Template with Access Database (Excel/Access 2003)

    When you get to the Add Tables dialog, click the Options button and make sure that all check boxes are ticked, then click OK. Does that help?

  7. #7
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Excel Template with Access Database (Excel/Access 2003)

    Hi Hans,
    Sorry for the delayed reply....not at work on the weekend.
    I'm not even getting the Add Tables dialog. The first window that comes up is : Choose Data Source
    Van
    ps. I am using Excel ver 2003 SP3

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Excel Template with Access Database (Excel/Access 2003)

    Since you mentioned that it worked well with another database, there appears to be a problem with your database. It's hard to tell what the cause could be without seeing it. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Excel Template with Access Database (Excel/Access 2003)

    Hi Hans,

    I've done some experimentation and have found that there is something in the name of the database that the excel import external data doesn't like. I changed the name from "HMO ver 8.31" to "HMO ver 831" and all worked well. Guessing Excel didn't like the full-stop in the title.

    Thanks again,
    Van

Posting Permissions

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