OFF97: Workbook Changed by MS Access May Not Be Recalculated

This article was previously published under Q172640
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
When you open a workbook in Microsoft Excel 97, some formulas in theworkbook may not be recalculated properly. For example, if cell B5 inSheet2 contains the following formula
=Sheet1!A1
the formula may return a value that was previously in Sheet1!A1 instead ofthe 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 changesto the workbook in Microsoft Excel 97, Microsoft Excel continues torecalculate the values properly. If you use Microsoft Access 97 to makeadditional changes to the workbook, you must use the workaround when youreopen the workbook in Microsoft Excel 97.
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access 97. We areresearching this problem and will post new information here in theMicrosoft 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 recordwhen the workbook is recalculated; if the record is not present, MicrosoftExcel 97 does not recalculate the values in workbook.

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

Note that this problem does not occur when you change a link table inearlier versions of Microsoft Access.
OFF97 XL97 AC97
Properties

Article ID: 172640 - Last Review: 10/07/2013 05:13:13 - Revision: 2.1

Microsoft Office 97 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbinterop kbprb KB172640
Feedback