ACC: "Incomplete calculation" Error Opening Linked MS Excel File

• Microsoft Access versions 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you open a Microsoft Excel spreadsheet file that contains links to another Microsoft Excel spreadsheet file, you receive one of the following messages:

   This document contains links. Re-establish links?

   The workbook you opened contains automatic links to information in
   another workbook. Do you want to update this workbook with changes
   made to the other workbook?

   '<filename.xls>' contains incomplete calculation.

CAUSE

The spreadsheet file that you are opening contains links to a second spreadsheet file, and the second spreadsheet file is the source of a linked table in a Microsoft Access database. You receive the error after you have updated the data in the Microsoft Access table that is linked to the second spreadsheet file.

In Microsoft Access version 7.0, this error occurs when you export to the Microsoft Excel 5-7 (*.xls) file format or to the Microsoft Excel 4 (*.xls) file format. In Microsoft Access 97, you do not receive this error unless you export the table to the Microsoft Excel 4.0 file format.

WORKAROUND

To prevent this error from occurring, follow these steps:

1. Start Microsoft Excel, and open the spreadsheet file that is the source of the links in the file that you are trying to open.

2. Press F9, or click Options on the Tools menu, and then click Calc Now on the Calculation tab.

3. Save and close the file.

4. Open the spreadsheet file containing the links to the file that you saved in step 3. Note that you do not receive an error message if you click Yes when prompted to update or re-establish the links.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Problem

Create a Microsoft Excel 4.0 Spreadsheet

1. Start Microsoft Access and open the sample database Northwind.mdb.

2. In the Database window, select the Customers table.

3. On the File menu, click Save As/Export.

4. In the Save As dialog box, click To An External File Or Database, and then click OK.

5. In the Save Table 'Customers' In dialog box, click Microsoft Excel 4 (*.xls) in the Save As Type box.

6. Type "Cust4.xls" (without the quotation marks) in the File Name box and then click Export.

Create a Spreadsheet Linked to Another Spreadsheet

1. Start Microsoft Excel and open the file Cust4.xls that you created in step 6 of the "Create a Microsoft Excel 4.0 Spreadsheet" section.

2. Press CTRL+HOME; then press CTRL+SHIFT+END to select the data in the spreadsheet.

3. On the Edit menu, click Copy.

4. On the File menu, click New. In the New dialog box, click the General tab, and then click the Workbook icon. Click OK to open a new workbook.

5. On the Edit menu, click Paste Special.

6. In the Paste Special dialog box, click Paste Link.

7. On the File menu, click Save.

8. In the Save As dialog box, click Microsoft Excel 4.0 Worksheet (*.xls) in the Save As Type box.

9. Type "Cust4a.xls" (without the quotation marks) in the File Name box, and then click Save. When you receive a message that the selected file type does not support workbooks containing multiple sheets, click OK to save only the active sheet.

10. Close both Cust4.xls and Cust4a.xls.

Create a Microsoft Access Table Linked to a Spreadsheet

1. Close both Cust4.xls and Cust4a.xls and return to Microsoft Access. Open the sample database Northwind.mdb if it is not already open.

2. On the File menu, point to Get External Data, and then click Link Tables.

3. In the Link dialog box, locate the file Cust4.xls that you created in in step 6 of the "Create a Microsoft Excel 4.0 Spreadsheet" section; select it, and then click Link.

4. When the Link Spreadsheet Wizard appears, click Next.

5. Click to select the First Row Contains Column Names check box, and then click Next.

6. Type "Cust4" (without the quotation marks) in the Linked Table Name box, and then click Finish.

7. When you receive the message that the Linked Spreadsheet Wizard has finished linking table Cust4, click OK.

8. Open the table Cust4. Make a change to the data in the ContactName field of the first record, and then close the table.

Reopen the Linked Spreadsheet in Microsoft Excel

1. Return to Microsoft Excel and open the file Cust4a.xls that you created in step 9 of the "Create a Spreadsheet Linked to Another Spreadsheet" section.

2. When you are prompted to re-establish links or to update this workbook with changes made to the other workbook, click Yes. Note that you receive the error described in the "Symptoms" section.

REFERENCES

For more information about linking to Microsoft Excel spreadsheet files, search the Help Index for "Microsoft Excel, importing and linking Excel data" and display the topic "import or link data from a spreadsheet."