Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Access and linked excel sheets! (Access 2003)

    Hi all,

    Please consider this scenario:

    I have a database that contains linked tables pointing to a excel spreadsheet, (lets call it Master). This master spreadsheet contains linked formulas to a number of smaller spreadsheet files that users use and update. Obviously the master spreadsheet only updates when it is opened when it prompts to Update Links. This is the problem I am facing as I would like the Access DB to always reflect the updated data. I do not want to update links manually in the master spreadsheet so my DB is updated too. How can I overcome this. What method or modification can I do to automate this and keep a DB thats always current?

    Tx
    Regards,
    Rudi

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

    Re: Access and linked excel sheets! (Access 2003)

    I don't think it is possible to have Access update the master workbook automatically, there is too much indirection. You could write code that uses Automation to open the workbook in Excel, update it, then save and close it. You'd have to run this code each time before opening the table (or a form based on it etc.)

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access and linked excel sheets! (Access 2003)

    Yes..this option came through in the discussion we had on this issue. I guess, if the person who raised this is going to do it, then this will be the option they must be satisfied with!
    Tx
    Regards,
    Rudi

Posting Permissions

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