In Lotus 1-2-3 Release 5 for Windows, you can open Excel .XLS, .XLT, and .XLW files created in Microsoft Excel Versions 2.1, 2.2, 3.0, and 4.0. You can also save a 1-2-3 Release 5 for Windows .WK4 file in .XLS or .XLW format.
If part or all of the Excel file is protected with a password, you must remove the password protection in Excel and then save the file before opening it in 1-2-3. If a .WK4 file is password-protected, this protection is lost when you save the file to .XLS or .XLW format. If you use macros to open Excel files in 1-2-3, it's recommended that you use the {FILE-OPEN} macro command instead of a /File Retrieve keystroke macro.
THE EXCEL LOG FILE
=======================
When you open Excel files in 1-2-3 or save a 1-2-3 file as an Excel file, 1-2-3 preserves as much of the original data as possible. If the file contains untranslatable information, functions or formulas that require editing, or links to other Excel files, 1-2-3 records their location and current value in a log file.
For example, if you open DATA.XLS in 1-2-3 and the file contains a function that has no equivalent in 1-2-3, 1-2-3 creates a log file called DATA.LOG in the same directory as DATA.XLS. If the directory already contains a file called DATA.LOG, the new file replaces the previous one. The log file indicates which functions and formulas did not translate and need editing in order to work.
When 1-2-3 creates a log file, you see a dialog box. Choose OK to see the translated file, or choose Explain to see the log file. 1-2-3 uses Notepad or Write to open the log file; you can also view the log file at any time using a word-processing program such as Lotus Ami Pro.
WHAT 1-2-3 DOESN'T TRANSLATE
============================
You can't open these Excel file types in 1-2-3:
Add-in macro files .XLA
Chart files .XLC
Macro files .XLM
Special DLL files .XLL
When you open an Excel file, 1-2-3 doesn't translate
* Charts and graphic objects
* What-if scenarios created with Scenario Manager
* Solutions created with Solver and Goal Seek, unless the Goal or Solution is kept
* OLE links
* Outlines
* Custom functions, formats, menus, dialog boxes, buttons, and icons
* Macro functions
* Custom functions created with Excel add-ins
* Hidden cells and rows
* Display settings except those for gridlines and gridline color
* Workspace settings
* Window protection and splits
* Worksheet views
* Frozen worksheet titles
* Page Setup settings including centering, black and white cells, page order, and scaling
* Named references, except absolute range names referring to contiguous areas above row 8193 or below 8192
WHAT'S DIFFERENT
================
When you open an Excel file, 1-2-3 translates these types of information as follows:
CELL NOTES
----------
1-2-3 converts Excel cell notes to range name notes created with 1-2-3 Classic. To display a table of cell notes in the translated file, follow these steps:
1. Select the cell where you want the table to start.
2. Press / to display 1-2-3 Classic.
3. Choose Range Name Note Table and press ENTER.
4. Choose Quit to close 1-2-3 Classic.
1-2-3 lists the cell notes in the file including their name, location, and contents. If you assigned a range name to a cell containing a note in Excel, 1-2-3 uses the range name. Otherwise, 1-2-3 names each note CELLNOTE1, CELLNOTE2, and so on. If the note contains more than 512 characters, 1-2-3 cuts off the remaining characters.
CHARACTER SETS
--------------
1-2-3 converts ANSI characters to their Lotus Multibyte Character Set (LMBCS) equivalent. 1-2-3 retains only ANSI characters created with the CHAR function and a value argument.
DATA TABLES
-----------
If you open an Excel file containing data tables, 1-2-3 puts @<<XL>>TABLE(ARGUMENTS); VALUE in each cell of each table. VALUE is the last calculated value saved in Excel before translation. To recreate the data tables in 1-2-3, choose Range Analyze What-if Table.
DATE CALCULATION
----------------
Excel lets you set the starting date from which all dates are calculated to either January 1, 1900 or January 2, 1904, while 1-2-3 uses only January 1, 1900. If you open an Excel file that uses 1904, 1-2-3 records it in the log file and assumes the dates start from January 1, 1900.
DDE AND FILE LINKS
------------------
If an Excel file contains DDE links, 1-2-3 converts the array formula that created the link to @DDELINK("Application","Filename","Range"). 1-2-3 puts the @function in the top left corner of each range that contains a DDE link. If the links provide data to the file from another Windows application, the translated file won't reflect changes to the original data unless the other application is installed on the same computer where you're running 1-2-3.
If an Excel file that you open in 1-2-3 contains links to other Excel files, the log file informs you that file links exist and lists the linked files with drive, path, file name, and a .WK4 extension. You must open each of the linked files in 1-2-3 and save them in their original locations as .WK4 or .WK3 files. Then you can edit the links to reference these files and use the Edit Links command in 1-2-3 to update all the file links.
If a 1-2-3 file that you save in Excel format contains links to other 1-2-3 files, the log file informs you that file links exist and lists the linked files with drive, path, file name and a .XLS extension. You must open each of the linked files in 1-2-3 and save them in their original locations as .XLS files. Then you can edit the links to reference these files and use the File Links command in Excel to update all the file links.
If a .WK4 file that you're saving as an Excel file contains links to .WK3 files, use the File Links command in Excel to refresh the link to the correct drive and path.
FORMATTING
----------
1-2-3 displays values larger than 9.99E+99 or smaller than 1E-99 as a row of asterisks (*). You can still calculate with these values, but if the results are larger than 9.99E+99 or smaller than 1E-99, 1-2-3 displays the results as a row of asterisks.
1-2-3 translates standard number formats to the closest available equivalent. If a file contains custom number formats, 1-2-3 converts them to the closest equivalent number format but doesn't preserve other custom formatting such as individual characters or text.
If you assigned a custom color to negative values, 1-2-3 displays them in red. All other colors assigned with custom number formats are lost. 1-2-3 converts cell borders, patterns, and colors to the closest available equivalent.
If the fonts used in the Excel file aren't available, 1-2-3 substitutes the closest available equivalents, and the status bar displays the name of the original font used to style the selected cell.
If the Excel file you open in 1-2-3 contains defined cell styles, 1-2-3 keeps the 16 styles used most frequently in the file and the first 35 characters of each style name. To see the defined styles available, choose Style Named Style or click the style selector on the status bar.
1-2-3 supports row heights of 1 to 255. 1-2-3 converts a height of 0 to 14, and heights of 256 through 409 to 255.
1-2-3 supports column widths of 0 to 240 and converts column widths of 241 through 255 to 240.
FORMULAS
--------
Because 1-2-3 calculates with precision to 18 digits, while Excel calculates with precision to 15 digits, some formulas may produce different results in the two programs.
1-2-3 and Excel use almost identical arithmetic and logical operators, except for the intersection operator in Excel, which 1-2-3 translates as @<<XL>>ISECT(RANGE1,RANGE2). 1-2-3 and Excel calculate using a slightly different order of precedence.
Results are identical except when you use exponentiation and negation in the same formula. Excel carries out exponentiation after negation, while 1-2-3 carries it out before negation. The difference is noticeable in an equation such as -2^2, which evaluates to 4 in Excel ((-2)^2) and to -4 in 1-2-3 (-(2^2)). If an .XLS file contains such formulas, 1-2-3 puts parentheses around the values to produce the same result as Excel.
Formulas that concatenate text may produce different results in the two programs because Excel concatenates text before comparisons, while 1-2-3 concatenates it after comparisons.
1-2-3 translates the error value #N/A to NA. 1-2-3 translates #VALUE!, #REF!, #NAME?, #NUM!, #DIV/0!, and #NULL! to ERR.
COLOR SETTINGS
--------------
When you open an Excel file in 1-2-3, the colors are translated as closely as possible to the 256 available colors in 1-2-3.
When you save a 1-2-3 file in Excel format, the colors are translated as closely as possible to the 16 standard default colors in Excel. For example, pale yellow in the 1-2-3 file will appear as white in the Excel file.
PRINT SETTINGS
--------------
1-2-3 translates Excel print areas that are defined as single, contiguous ranges, and retains header and footer settings that it supports, such as date and file name. 1-2-3 converts Excel header and footer formatting codes to 1-2-3 codes as listed below:
Excel code 1-2-3 code
---------- ----------
&& &
&B b and end-of-format sequence
&C |Text|
&D @
&F ^
&I i and end-of-format sequence
&L Text|
&N no equivalent available
&P #
&R |Text
&T +
The Excel page numbering feature "Page n of n" (for example, Page 2 of 5) does not get translated when you open an Excel file in 1-2-3.
ROWS AND COLUMNS
----------------
Both 1-2-3 and Excel display 256 columns. However, a 1-2-3 worksheet contains 8,192 rows, while an Excel worksheet has 16,384 rows.
When you open an Excel file that contains data in rows 8,193 to 16,384, 1-2-3 inserts a second worksheet and puts the data in it. If you save this 1-2-3 file as an Excel file again, you must save it in .XLW format, and the data, formerly in one sheet in Excel, will now appear in two consecutive sheets.
Formula references to data ranges that span multiple worksheets after 1-2-3 has split the data evaluate to ERR. To avoid problems, edit the file in Excel before you open it in 1-2-3. Move any ranges referenced by formulas so that the data is either completely above row 8,193 or below row 8,192.
For information on how 1-2-3 translates row height and column width, see "FORMATTING" above.
SHEET NAMES AND RANGE NAMES
---------------------------
Sheet names in Excel bound workbook files can contain hyphens; 1-2-3 sheet names cannot contain hyphens. When you open an Excel bound workbook file in 1-2-3, sheet names containing hyphens are translated to the appropriate sheet letter according to the position of the sheet in the file.
Range names in 1-2-3 can contain spaces and hyphens; range names in Excel cannot contain these characters. When you open a 1-2-3 file that was saved as an Excel file, Excel prompts you to replace spaces and hyphens in range names with another accepted character, such as an underscore (_).
TRANSLATING THE =FIXED AND @STRING FUNCTIONS
-------------------------------------------
When you open an Excel file in 1-2-3, the =Fixed function translates to @String; when you save a 1-2-3 file as an Excel file, the @String function translates to =Fixed. Some unexpected results, not reported in the log file, can occur during translation of these functions.
1-2-3 lets you to specify up to 116 decimal places for n in @String; Excel allows up to 127 decimal places. If the number of decimal places specified in the =Fixed function in Excel is greater than 116, this function appears as ERR when you open the Excel file in 1-2-3.
If n in the @String function is negative, the number returned by @String appears as Scientrific Notation in 1-2-3. When you save the 1-2-3 file as an Excel file, this number appears rounded to the left of the decimal point. In Excel, if the decimals argument to =Fixed is negative, the number returned appears rounded to the left of the decimal point. When you open an Excel file in 1-2-3, this number appears as scientific notation.
To format the number returned by =Fixed with commas, you set the no_commas argument to FALSE. When you open the Excel file in 1-2-3, the number appears without commas, even though no_commas was set to FALSE. The argument n+1000 in the @String function indicates comma format. This argument causes the =Fixed function to appear as #VALUE! when you open the file in Excel. The argument n+10000 in the @String function indicates General format. This argument also causes the =Fixed function to appear as #VALUE! when you open the file in Excel.
