G
Good Try
Can someone please help me?
I have an Excel (2000) template that has a lot of formulas and a lot of data
entry cells. Every sheet in the book is protected. Every Cell in the book
is locked except data entry cells. Every data entry cell has data
validation.
I create some workbooks based on this template from a procedure in an access
database. Later I retrieve data from these books using the
Transferspreadsheet method from Microsoft Access.
Whenever I open a book from within code in MS Access, many of the book's
cells contain "#Value!". Each of the cells contain valid formulas. If I
open the book manually, I can cause each of these cells to recalculate by
pressing CTRL-ALT-F9. No other method seems to work.
In code, I have tried to call the Calculate and/or CalculateFull methods on
the instance of Excel. I have tried calling the Calculate method on each
sheet in the book. I have tried calling the Calculate method on the
UsedRange property of each sheet in the book. I have iterated across all
the sheets in the book, removing the protection from each and tried the
above methods. Nothing I try to call from code results in these formulas
recalculating. I have verified that the EnableCalculation property of each
sheet is set to True.
What am I missing here? What else could be preventing Excel from
recalculating?
Please help if you can.
--
Kerry Carroll
Please remove the spaces from the following address if you need to reply to
me directly
kerry . carroll @ cinbell . com
I have an Excel (2000) template that has a lot of formulas and a lot of data
entry cells. Every sheet in the book is protected. Every Cell in the book
is locked except data entry cells. Every data entry cell has data
validation.
I create some workbooks based on this template from a procedure in an access
database. Later I retrieve data from these books using the
Transferspreadsheet method from Microsoft Access.
Whenever I open a book from within code in MS Access, many of the book's
cells contain "#Value!". Each of the cells contain valid formulas. If I
open the book manually, I can cause each of these cells to recalculate by
pressing CTRL-ALT-F9. No other method seems to work.
In code, I have tried to call the Calculate and/or CalculateFull methods on
the instance of Excel. I have tried calling the Calculate method on each
sheet in the book. I have tried calling the Calculate method on the
UsedRange property of each sheet in the book. I have iterated across all
the sheets in the book, removing the protection from each and tried the
above methods. Nothing I try to call from code results in these formulas
recalculating. I have verified that the EnableCalculation property of each
sheet is set to True.
What am I missing here? What else could be preventing Excel from
recalculating?
Please help if you can.
--
Kerry Carroll
Please remove the spaces from the following address if you need to reply to
me directly
kerry . carroll @ cinbell . com