HELP: Link table 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 the spreadsheet 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!

 

[Guest]

hi,
it is to my understanding that xl formulas don't calculate
unless the file is in memory(file open).nor do any other
appications. while on the disk(file closed), everything is
just stored "as is". that is the importance of saving. so
i see no way around your delima except to beef up your
hardward. more memory so that you can have both open at
the same time.
good luck.

 

[Gorb]

hi,
it is to my understanding that xl formulas don't calculate
unless the file is in memory(file open).nor do any other
appications. while on the disk(file closed), everything is
just stored "as is". that is the importance of saving. so
i see no way around your delima except to beef up your
hardward. more memory so that you can have both open at
the same time.
good luck.

Well, memory is not an issue. Plus, both cannot be opened at the same
time, because the Excel file is locked when Access links to it and is
active. Gotta close the link or the Access application itself in order
to open and save the Excel file. If this is the only solution, then...
bummer... lots of ugly coding and possible instance conflicts.