OFF97: Workbook Changed by MS Access May Not Be Recalculated

Last reviewed: August 13, 1997
Article ID: Q172640
The information in this article applies to:
  • Microsoft Office 97 for Windows
  • Microsoft Excel 97 for Windows
  • Microsoft Access 97

SYMPTOMS

When you open a workbook in Microsoft Excel 97, some formulas in the workbook may not be recalculated properly. For example, if cell B5 in Sheet2 contains the following formula

   =Sheet1!A1

the formula may return a value that was previously in Sheet1!A1 instead of the value that is currently in Sheet1!A1.

CAUSE

This problem occur if all of the following conditions are true:

  • In Microsoft Access 97, you create a link table that links to a worksheet in a Microsoft Excel workbook.

    -and-

  • By using Microsoft Access 97, you change a value (or values) in the link table.

    -and-

  • You open the workbook in Microsoft Excel 97.

    -and-

  • One or more formulas in the workbook refer to the changed values.

WORKAROUND

To work around this problem, follow these steps:

  1. In Microsoft Excel 97, open the workbook.

  2. Press CTRL+ALT+F9.

The values in the workbook are recalculated. If you make additional changes to the workbook in Microsoft Excel 97, Microsoft Excel continues to recalculate the values properly. If you use Microsoft Access 97 to make additional changes to the workbook, you must use the workaround when you reopen the workbook in Microsoft Excel 97.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

In Microsoft Excel 97, when a workbook must be recalculated, a record named "RTUNCALCED" is written in the workbook. Microsoft Excel clears this record when the workbook is recalculated; if the record is not present, Microsoft Excel 97 does not recalculate the values in workbook.

When you make a change to a link table in Microsoft Access 97, the change is written to the table in the external file. However, if the external file is a Microsoft Excel workbook, Microsoft Access 97 does not write the RTUNCALCED record in the workbook. As a result, when you open the workbook in Microsoft Excel 97, the workbook is not automatically recalculated, and formulas in the workbook may return incorrect values.

Note that this problem does not occur when you change a link table in earlier versions of Microsoft Access.


Additional query words: OFF97 XL97 AC97
Keywords : OffInterop xlloadsave xlui kbinterop
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb
Solution Type : kbworkaround


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 13, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.