Update Calculated Data from Linked Excel Table

[simon1974]

I have an Excel table that is linked to Access. One of the fields is calculated (in Excel). I am able to update the data from the entry fields but must open, save, and close the Excel file in order for the calculated amount to appear as updated in my linked table. Is their any way to call a function that will force the calculated fields to update? I tried linked table manager and the functionality there does not do the trick. I am willing to explore an "AfterUpdate" event that triggers some VB code... I just need some help on what commands to use. Thanks.

 

[WildHare]

If you LINK the excel worksheet into Access, then Open the "table", and make changes to the data, the formula on the worksheet will automatically be updated, because you are editing the actual worksheet data.

You just have to be careful, because the 'link' brings in the whole sheet, so one of your "records" in the table is the formula result.
You WILL not see that guy updated until you close and then reopen the Access 'table'

But basically, to resolve this, edit changes are posted directly to an Excel workbook when linked as a table.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[simon1974]

Jim,

Thanks for your help... actually it seems that in order to get the formula value to update, I have to open the EXCEL sheet, save it, and then reopen the table. Closing and reopening the table doesn't seem to do the trick. Not sure if I'm missing something. Thanks.

Sean