XL: Tips for Importing Lotus 1-2-3 Files to Excel

The information in this article applies to:

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, and 5.0

SUMMARY

This article contains information about potential issues you may encounter when you import Lotus 1-2-3 files into Microsoft Excel.

Opening WK4 Files

To open Lotus 1-2-3 WK4 files in Microsoft Excel version 5.0, you must obtain the Lotus 1-2-3 WK4 file converter. The Lotus 1-2-3 WK4 file converter is available in the "Lotus 1-2-3 WK4 File Converter" (WE1130) Application Note.

You can obtain this Application Note from the following sources:

• Microsoft's World Wide Web Site on the Internet
• The Internet (Microsoft anonymous ftp server)
• Microsoft Download Service (MSDL)
• Microsoft Technical Support
• Microsoft FastTips Technical Support Library

   ARTICLE-ID: Q122583
   TITLE     : WE1130: Lotus 1-2-3 WK4 File Converter

   ARTICLE-ID: Q148140
   TITLE     : File Conversion Wizard Does Not Run Under MS Excel for NT

Formatting

When you open a Lotus 1-2-3 worksheet or workbook, Microsoft Excel applies the formatting stored in the associated .fmt, .fm3, or .all formatting file. Be sure that the associated formatting file is stored in the same folder as the .wk? file. If you resave a Lotus 1-2-3 file in the Microsoft Excel (.xls) format, Microsoft Excel saves the spreadsheet data and formatting in a single workbook file.

Objects

In Microsoft Excel 5.0, when you open a Lotus 1-2-3 WK4 file, any drawing objects, such as macro buttons, text boxes, and lines, are not converted. Additionally, worksheet names are displayed as the Lotus 1-2-3 default: A, B, C, and so on. The Lotus 1-2-3 WK4 file converter does not convert drawing objects on a worksheet. This includes any of the following items:

   arc
   arrow
   button
   ellipse
   embedded object
   freehand
   line
   polygon
   polyline
   rectangle
   rounded rectangle
   text

Charts

Microsoft Excel 4.0 and Later:

In Lotus 1-2-3, versions 3.x and later, you can create a graph on a chart sheet or create the chart as an object on the worksheet. In Lotus 1-2-3, version 2.x, if you use the WYSIWYG add-in, you can place a graph on a worksheet.

By default, Microsoft Excel automatically converts any charts associated with a Lotus 1-2-3 worksheet. You can use the following setting in the Excel4.ini and Excel5.ini files to suppress the creation of chart sheets when you open a Lotus 1-2-3 file:

   Load_Chart_Wnd=0

Microsoft Excel 2.x and 3.x:

When you import a file that contains a graph, Microsoft Excel 2.x and 3.x display a prompt for each graph that is stored with the worksheet or is located on the worksheet. You are prompted whether to convert the file to a Microsoft Excel chart. If you click Yes, Microsoft Excel creates a new chart window. In Microsoft Excel 3.x, you can then copy the chart to the worksheet.

Databases

The Database, Criteria, and Extract defined ranges are successfully imported and function properly. However, database criteria ranges are evaluated differently when you extract data, find data, and use database functions. For example, the "John" criteria finds only rows with cells that contain "John." If you clear the Transition Formula Evaluation check box, the "John" criteria finds any rows that contain cells with values beginning with "John"; for example, cells that contain "John," "Johnson," and "Johnsen" are found.

Calculations

Whenever you open a Lotus 1-2-3 file, the Transition Formula Entry check box is selected. When this feature is selected, Microsoft Excel converts formulas that are entered with Lotus 1-2-3 syntax to Microsoft Excel syntax and makes names defined in Microsoft Excel behave as defined names do in Lotus 1-2-3.

Microsoft Excel calculates formulas differently from Lotus 1-2-3. When a cell that contains text is used in a formula, Lotus 1-2-3 assigns a value of 0 (zero) to the cell. In Microsoft Excel, you cannot combine text and numeric entries in the same formula. However, when you use a worksheet function in Microsoft Excel, a value of 0 is assigned to cells that contain text. For example, if you clear the Transition Formula Evaluation check box, and you type text in cell A1 and the value 100 in cell B1; the formula =A1+B1 returns the #VALUE! error value. However, the worksheet formula =SUM(A1,B1) returns the value 100.

Lotus 1-2-3 evaluates Boolean expressions to 0 or 1 and displays 0 or 1 in the cell. For example, in Lotus 1-2-3, the expression 2<3 displays 1 in the cell to represent True; Microsoft Excel displays True or False in the cell. If you select the Transition Formula Evaluation check box, Microsoft Excel displays 0 for False and 1 for True.

Some functions, including @MOD, @VLOOKUP, and @HLOOKUP, are evaluated differently. For example, the @VLOOKUP function in Lotus 1-2-3 searches for an exact match in the first column; the VLOOKUP worksheet function in Microsoft Excel assumes the first column is sorted and finds the closest value in the first column that does not exceed the lookup value. The VLOOKUP and HLOOKUP worksheet functions in Microsoft Excel include a fourth argument, range_lookup. If you set this argument to False, Microsoft Excel searches for an exact match.

To force Microsoft Excel to calculate formulas as Lotus 1-2-3 does, use the following steps:

1. On the Tools menu, click Options. Click the Transition tab.

2. Click Transition Formula Evaluation, and click OK.

Calculation Order

Mathematical Order of Precedence Differences

This table compares the mathematical operators used by Microsoft Excel and Lotus 1-2-3.

                     Lotus                 Microsoft
   Operator          1-2-3      Precedence   Excel      Precedence
   ---------------------------------------------------------------
   Exponentiation    ^             1st        ^            2nd
   Positive and      + and -       2nd        + and -      1st
     negative
   Multiplication    * and /       3rd        * and /      3rd
     and division
   Addition and      + and -       4th        + and -      4th
     Subtraction
   Comparison        = < >         5th        = < >        5th
                     <= >=                    <= >=
   Logical NOT       #not#         6th        NOT()        6th
   Logical AND       #and# and     7th        AND() and    7th
     and OR          #or#                     OR()
   String            &             7th        &            7th
     concatenation

Links

In Microsoft Excel, when you open a Lotus 1-2-3 .wk4 file that contains a link to another file, the cells may be updated with a #REF! error value. To update an external link in a Lotus 1-2-3 .wk4 file, do the following:

1. In Microsoft Excel, click Links on the Edit menu.

2. In the Links dialog box, select the link that you want to update. Click Update Now.

   NOTE: If you want to open the source document, click Open. This also updates the external link.

Converting Dates

Microsoft Excel and Lotus 1-2-3 use the same serial date systems. The serial values in Microsoft Excel 7.0 and earlier range from 0 (1/1/1900) to 65380 (12/31/2078). Serial date values in Lotus 1-2-3 range from 0 to 73050 (12/31/2099). If you import a date from Lotus 1-2-3 that contains a date function with a year later then 2078, the function returns a #NUM! error value. If you import a worksheet that contains a formatted date with a year later than 2078, Microsoft Excel fills the cell with 255 number signs (#).

NOTE: This problem does not exist in Microsoft Excel 97 because it allows dates up to the year 9999.

Macros

Macros in Lotus 1-2-3 are stored directly on the worksheet. This is different from how Microsoft Excel stores macros. Microsoft Excel stores macros on a macro sheet (in Microsoft Excel 4.0) or in a module sheet for macros written in Visual Basic for Applications for Microsoft Excel 5.0 and later.

Microsoft Excel 4.0a and Later:

Microsoft Excel can run Lotus 1-2-3 macros directly. You do not have to translate (rewrite) the macro. When you open a Lotus 1-2-3 file that contains macros, you can see a list of all the available 1-2-3 macros by looking at the list of defined names for that workbook. To run the Lotus 1-2-3 macro, press CTRL and the associated letter for the macro. For example, press CTRL+P.

Microsoft Excel 4.0 and Ealier:

To convert (rewrite) Lotus 1-2-3 macros to Microsoft Excel macros, do the following:

1. In Microsoft Excel, open the Lotus 1-2-3 worksheet that contains the macro.

2. On the Control menu, click Run. To open the Control menu, press ALT+SPACEBAR.

3. Click Macro Translator, and then click OK.

4. On the Translate menu, click Lotus 1-2-3. Select the name of the worksheet you want to convert.

5. Select the name of the macro that you want to convert. If you want the translator to list the 1-2-3 macro beside the converted Microsoft Excel macro, click the Verbose option.

REFERENCES

For more information about converting Lotus 1-2-3 files, click the Index tab in Microsoft Excel 97 Help, type the following text

   Lotus 1-2-3, converting files

For more information about importing worksheets, formulas, references, and formats from specific versions of Lotus 1-2-3 see "Switching to Microsoft Excel from Lotus 1-2-3," version 4.0, pages 24-31, 38, or the "User's Guide," version 3.0, pages 41-64.