Access and linked excel sheets! (Access 2003)

[Rudi]

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

[HansV]

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.)

[Rudi]

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