Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    linking w/ excel (9.0/2001)

    Is there a way to link an Access database and Excel workbook together so that as the database gets updated, so does the workbook? I know excel feeds into Acess, but does the opposite?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking w/ excel (9.0/2001)

    Instead of import an excel sheet/workbook, you can link it.
    In Access use File / Get External Data / Link Tables. In the dialog window change Files Of Types to Microsoft Excel and navigate to your xls file.
    Clicking on Link will take you through a wizard to link the table.
    Once this is done, changes in Access and Excel will be stored in the Excel file and always be reflected in Access.
    Francois

  3. #3
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking w/ excel (9.0/2001)

    now, will I have to make the changes in Excel? Because what I want the excel file for is to make a line graph based off equations that I put on a diff. worksheet, derived from access. There will be no changes made in Excel to reflect in access. All the changes need to be made in Access and reflect in excel--will what you said do that?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking w/ excel (9.0/2001)

    When you link a table to Excel, you store the data in the excel file. Not in Access. However, the linked table will work all the same as a normal table stored in the Access database. No need to have two places where the data is stored.

    If you don't want to store your data in excel, you can use excel to go search for Access data.
    In Excel, use Data / Get External Data / New Database Query and follow the wizard to get the data.
    LOOK OUT : each time that you change the data in access, you'll have to do a Refresh Data in the Data menu of excel.
    Now you can create your graph in Excel.

    PS: You can also insert graph on a form or report in access. I don't know if the to graph applications are the same in excel and access.

    Hope this make things a little easier.
    Francois

  5. #5
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking w/ excel (9.0/2001)

    Thank you

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: linking w/ excel (9.0/2001)

    There is also another method of creating charts and graphs in Excel from Access. It involves using Automation so that Access generates the data in Excel and then creates the chart. To use it you need to know the Object Model in Excel pretty well, but it is a very powerful tool, as the graph capabilities in Excel exceed those native to Access.
    Wendell

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: linking w/ excel (9.0/2001)

    Where would one learn Excel automation in Access, I presume that's what you meant.

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

    Re: linking w/ excel (9.0/2001)

    <!profile=WendellB>WendellB<!/profile>'s profile has a link to his website. Look for Support > Tutorials. There is a short tutorial on automation, with lots of useful links. Be sure to download Microsoft's Automation help file (for Office 2000 or for Office 97; the Office 2000 version applies to Office XP and probably 2003 too.)

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: linking w/ excel (9.0/2001)

    Thanks Hans and Wendell, this looks like a great starting point.

  10. #10
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking w/ excel (9.0/2001)

    Just a curious thought, but would the TransferSpreadsheet action allow this? It seems to imply you can export to a predefined range in a file? I'm about to try something similar so was wondering if it was viable?

    Ian

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

    Re: linking w/ excel (9.0/2001)

    From the online help (for Access 2002 VBA):
    <hr>Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.<hr>
    If you use TransferSpreadsheet to export data from Access to Excel, you can't specify the Range argument. The result is a new worksheet; if a worksheet with the same name already exists in the workbook, it is overwritten.

  12. #12
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking w/ excel (9.0/2001)

    Amazing how word blindness can effect you when you want something to be different to what it really is.....

    Ah well, saved me a few more bald patches, thanks Hans <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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