Microsoft KB Archive/166763

= XL97: Defined Names Saved Incorrectly in Lotus 1-2-3 Format =

Article ID: 166763

Article Last Modified on 10/22/2000

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q166763



SYMPTOMS
In Microsoft Excel 97, when you save a file in the Lotus 1-2-3 WK4, WK3, or WK1 file format, the following problems may occur:  When you reopen the file in Microsoft Excel, defined names within the workbook refer to incorrect ranges. However, the workbook functions correctly in other respects. When you open the file in Lotus 1-2-3, you receive the following error message:

Error reading file

Although you can open the file, much (or all) of its data is missing.



CAUSE
These problems occur when either of the following conditions is true:  A defined name in the workbook refers to an entire column, or to multiple columns of cells.

For example, the defined name refers to the following cells:

=Sheet1!$A:$A

=Sheet1!$D:$K

-or-

 A defined name in the workbook refers to a cell (or cells) beyond row 8,192.

For example, the defined name refers to the following cells:

=Sheet1!$A$8195

=Sheet1!$D$65536





WORKAROUND
To prevent these problems from occurring, verify that defined names in the workbook do not refer to entire columns of cells or cells beyond row 8,192 before you save the workbook in the Lotus 1-2-3 WK4, WK3, or WK1 format.

<div class="status_section">

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

<div class="moreinformation_section">

MORE INFORMATION
In Microsoft Excel 97, worksheets may contain up to 65,536 rows. In the Lotus 1-2-3 WK4, WK3, and WK1 file formats, worksheets may contain up to 8,192 rows. Because of this difference, you may experience problems when you open files that are saved in a Lotus 1-2-3 file format by Microsoft Excel 97. In particular, defined names may be affected by the process.

In Microsoft Excel 97, when you open a Lotus 1-2-3 file that you saved from Microsoft Excel 97, defined names are affected in the following ways:   If a defined name refers to a cell (or cells) after row 8,192, the row numbers of the cells are decremented in units of 8,192 rows until the row numbers are less than or equal to 8,192.

Examples of row numbers that are changed are listed in the following table. <pre class="fixed_text">    Original Range Used            Range Used After Reopening -

=Sheet1!$A$1:$A$8195          =Sheet1!$A$1:$A$3 =Sheet1!$A$8193:$A$8200       =Sheet1!$A$1:$A$8 =Sheet1!$A$8190:$A$8195       =Sheet1!$A$3:$A$8190 </li>  If a defined name refers to an entire column (or columns), or to a cell (or cells) in row 65,536, the defined name is changed so that it refers to the same row numbers, but the row numbers are in columns A through H.

Examples of columns that are changed are listed in the following table. <pre class="fixed_text">     Range Originally Used          Range Used After Reopening -

=Sheet1!$A:$A                 =Sheet1!$A:$H =Sheet1!$K:$X                 =Sheet1!$A:$H =Sheet1!$M$65536              =Sheet1!$A$65536:$H$65536 </li></ul>

If you attempt to open a Lotus 1-2-3 WK4, WK3, or WK1 file that you saved with this kind of defined name in an earlier version of Microsoft Excel, you cannot open the file. However, the defined names still refer to incorrect ranges.

If you attempt to open a Lotus 1-2-3 WK4, WK3, or WK1 file that you saved with this kind of defined name in Lotus 1-2-3, you may receive the error message in this article. If this problem occurs, close the file without saving changes. You can then reopen the file in Microsoft Excel and correct the problem with the defined names.

Additional query words: XL97 .wk4 .wk3 .wk1

Keywords: kbprb KB166763

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.