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.
NOTE: Microsoft Excel 7.0 and Microsoft Excel 97 include a
Wk4 file converter. The converter included with Microsoft Excel 7.0 allows you
to read the Lotus 1-2-3 Wk4 file format. The converter in Microsoft Excel 97
allows you to read and write the Wk4 format.
Formatting
When you open a Lotus 1-2-3 worksheet or workbook, Microsoft
Excel applies the formatting stored in any associated .fmt, .fm3, or .all
files. 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
NOTE: This problem does not occur in later versions. All objects are
converted when you open them in Microsoft Excel 7.0 and later.
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
Because Microsoft Excel can read Impress (.fm3) files and Allways
(.all) formatting files, you can import a Lotus 1-2-3 worksheet that contains a
chart on the worksheet. The chart appears on the worksheet as it does in Lotus
1-2-3.
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, a criteria of "John" finds only rows with cells that
contain "John." If you clear the
Transition Formula Evaluation check box, a criteria of "John" finds 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 cause Microsoft Excel to calculate formulas as
Lotus 1-2-3 does, follow these steps:
- On the Tools menu, click Options. Click the Transition tab.
- 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
NOTE: Lotus 1-2-3 evaluates the exponentiation operator (^) before the
negation operator (-). Microsoft Excel evaluates the negation operator first.
For example, in Lotus 1-2-3, the formula =-2^4 returns the value -16, but
returns 16 in Microsoft Excel. To correct this difference, use parentheses to
change the order of evaluation; for example, =-(2^4) produces -16.
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, follow these
steps:
- In Microsoft Excel, click Links on the Edit menu.
- 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.
To avoid this behavior, save the file in the Microsoft Excel
workbook format.
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:
Some earlier
versions of 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.
NOTE: Excel 97 with Security update (8.0h and later), and Excel 2000
do not run Lotus 1-2-3 macros.
Microsoft Excel 4.0 and Earlier:
To convert (rewrite) Lotus 1-2-3 macros to Microsoft Excel macros,
do the following:
- In Microsoft Excel, open the Lotus 1-2-3 worksheet that
contains the macro.
- On the control menu, click Run. To open the control menu, press ALT+SPACEBAR.
- Click Macro Translator, and then click OK.
- On the Translate menu, click Lotus 1-2-3. Select the name of the worksheet you want to convert.
- 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.
Microsoft Excel places the converted macro on a new macro
sheet.