HELP: Access table linked to Excel - calculated fields?

[K Zox]

The problem is as follows:

I linked an Access table to a complex Excel spreadsheet (.xls). I also
built a custom Access form to browse and modify the data in that
table. Works fine, however the worksheet also has numerous calculated
cells (with formulas), and these formulas have no effect while I am
using the form. The only way to correctly update these cells is to
close my Access application, open the spreadsheet in Excel and save
it, which is a hardly acceptable process.

Is there a way for those formulas to update the corresponding cells
automatically, without having to close Access and open the sheet?

Thanks in advance!

 

[Jamie Collins]

K Zox wrote ...

I linked an Access table to a complex Excel spreadsheet (.xls). I also
built a custom Access form to browse and modify the data in that
table. Works fine, however the worksheet also has numerous calculated
cells (with formulas), and these formulas have no effect while I am
using the form. The only way to correctly update these cells is to
close my Access application, open the spreadsheet in Excel and save
it, which is a hardly acceptable process.

Is there a way for those formulas to update the corresponding cells
automatically, without having to close Access and open the sheet?

Well, I don't see that you'd have to close MS Access, just release
your connection/locks to the workbook. But, yes, the only way to
recalculate the formulas is to open the workbook in the Excel app and
trigger calculation from there. You could do this in an invisible
instance of Excel using automation from your MS Access app.

Jamie.

--

 

[Gorb]

K Zox wrote ...


Well, I don't see that you'd have to close MS Access, just release
your connection/locks to the workbook. But, yes, the only way to
recalculate the formulas is to open the workbook in the Excel app and
trigger calculation from there. You could do this in an invisible
instance of Excel using automation from your MS Access app.

Jamie.

I see. Thank you for the info, Jamie. While I've seen many posts
related to opening/closing Excel app from within Access, I don't quite
understand how I could drop my linked table and then recreate it
programmatically (if this is the only way...) Any suggestions or code
examples?

TIA!

 

[Jamie Collins]

I see. Thank you for the info, Jamie. While I've seen many posts
related to opening/closing Excel app from within Access, I don't quite
understand how I could drop my linked table and then recreate it
programmatically (if this is the only way...) Any suggestions or code
examples?

TIA!

Here's a MSDN link for you:

http://support.microsoft.com/default.aspx?scid=kb;en-us;240222
How To Use ADO to Refresh/Create Linked Table

I don't use linked tables very often myself, therefore I suggest you
post your request in one of the MS Access newsgroups for an
alternative perspective (at the very least you should get a link to
the equivalent *DAO* code <g>).

Jamie.

--