You can open .XLS files created in Microsoft Excel Versions 2.1, 2.2, 3.0, and 4.0 in Lotus 1-2-3 Release 4. If part or all of the .XLS 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.
When you open an .XLS file in 1-2-3, 1-2-3 displays the message "File and/or extension converted," creates a copy of the file in .WK4 format, and leaves the original .XLS file unchanged. To keep a copy of the new .WK4 file, save the file before you close it or end the 1-2-3 session. If, however, a .WK4 file with the same name already exists in the directory containing the .XLS file, 1-2-3 displays a message and opens the file as a read-only .WK4 file. To save the read-only file, choose File Save As and either assign a different file name or choose a different directory.
If you use macros to open .XLS files in 1-2-3, it's recommended that you use the {FILE-OPEN} macro command instead of a /File Retrieve keystroke macro.
1-2-3 preserves as much of the original data as possible. If the .XLS file contains untranslatable information, formulas that require editing, or links to other .XLS 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. You must edit untranslated functions and formulas to use them in 1-2-3.
When 1-2-3 creates a log file, you see a dialog box. Choose OK to see the translated .XLS file, or choose Explain to see the log file. You can view the translated file after 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 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
Template files .XLT
Workbook files .XLW
To use a bound workbook file in 1-2-3, unbind the file in Excel and save each worksheet as a separate .XLS file. Then open the individual .XLS files in 1-2-3.
When you open an .XLS 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
* OLE links
* Outlines
* Custom functions, formats, menus, dialog boxes, buttons, and icons
* Macro functions
* 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
WHAT'S DIFFERENT
================
When you open an .XLS 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. For more information on how 1-2-3 translates CHAR, see "FUNCTIONS" below.
DATA TABLES
-----------
If you open an .XLS file containing data tables, 1-2-3 puts @<<XL>>TABLE(ARGUMENTS);VALUE in each cell of the tables. 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 .XLS 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 .XLS 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 contains links to other .XLS files, 1-2-3 retains the file name and range, and converts the .XLS extension to .WK4. If the Excel file contains links to .WK3 files, 1-2-3 retains the links and leaves the .WK3 extension unchanged. Cells containing these file links evaluate to ERR in 1-2-3. Before you can reestablish the file links, either copy the referenced files to the same directory as the translated Excel file, or change the default directory in 1-2-3 to the directory containing the referenced files. 1-2-3 refreshes the .WK3 file links for you. To reestablish each translated .XLS file link, translate the associated .XLS file and then save it as a .WK4 file. After you translate all of the associated .XLS files, choose Edit Links in 1-2-3 to refresh the links.
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 .XLS 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 .XLS file 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 precision is 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.
FUNCTIONS
---------
1-2-3 converts Excel functions to equivalent 1-2-3 @functions. Sometimes, 1-2-3 translates different Excel functions to the same @function but uses different arguments. If no equivalent is available, 1-2-3 converts the Excel function to @<<XL>>FUNCTION(ARGUMENTS);VALUE where VALUE is the last calculated value saved in Excel before translation. Cells containing untranslatable functions evaluate to ERR in 1-2-3. If the untranslated Excel function is supported by an add-in that you can use with 1-2-3, it evaluates correctly when you load the add-in.
To use untranslatable functions in 1-2-3, do one of the following: build a formula that recreates the original function using existing @functions and commands, write an add-in @function using an add-in development application such as the 1-2-3 Release 4 Add-In Development Kit, or use the last calculated value.
1-2-3 translates only array functions such HLOOKUP, VLOOKUP, and SUMPRODUCT that take range arguments. If a file contains other array functions, 1-2-3 puts @<<XL>>FUNCTION(ARGUMENTS);VALUE in each cell of the array. These cells evaluate to ERR. For information on DDE links, which Excel creates with array formulas, see "DDE AND FILE LINKS" above.
1-2-3 translates FIND, MATCH, FASTMATCH, and WEEKDAY to equivalent 1-2-3 @functions but alters the results to return the same values as Excel. For example, 1-2-3 translates WEEKDAY to @WEEKDAY but increases the result by a factor of 2 because Excel uses 1 to 7 for Sunday through Saturday, while 1-2-3 uses 0 to 6 for Monday through Sunday. If you use the equivalent 1-2-3 @functions in new calculations, you must adjust any dependent formulas to reflect the way these @functions work in 1-2-3.
Some Excel functions use different value arguments than the equivalent 1-2-3 @functions. For example, in Excel, MATCH can take 0, 1, or -1 as an optional type argument while in 1-2-3, @MATCH can take 0, 1, or 2. In such cases, 1-2-3 translates the Excel function and converts its arguments to produce the same result as Excel. In cases where the value argument in Excel is the result of a formula or cell reference, 1-2-3 converts the argument to the 1-2-3 equivalent and doesn't retain the formula or cell reference. To translate such functions correctly, make sure you recalculate the .XLS file before saving it in Excel and then opening it in 1-2-3.
1-2-3 translates DB to @DB and assumes a value of 12 for the Excel month argument. Because 1-2-3 produces more accurate results, the translated function evaluates differently in 1-2-3.
1-2-3 translates CHAR to @CHAR and returns the same ANSI character as Excel when you use a value argument. If CHAR takes a cell reference or formula argument, the translated function returns the LMBCS character equivalent to the value in the referenced cell or the formula result. 1-2-3 annotates the cell containing the translated function by adding a semicolon after the function, followed by the original ANSI character. For example, if A1 contains =CHAR(A2) and A2 contains the value 181, A1 displays ╡ in Excel. When you open the file containing this function in 1-2-3, 1-2-3 translates the function to @CHAR(A2);╡. The cell containing the translated function displays ┴, the LMBCS equivalent of 181.
1-2-3 translates INTERCEPT, RSQ, and STEYX to @REGRESSION. 1-2-3 assumes the x- and y-range values are in columns and that the y-range contains the same number of rows as the x-range. If the x- or y-range data is in rows, @REGRESSION evaluates to ERR. To display the correct result, transpose the x- and y-range data to appear in columns instead of rows.
1-2-3 doesn't have equivalent @functions for MINVERSE or MMULT. Instead, use the Range Analyze Invert Matrix or Range Analyze Multiply Matrix commands.
For information on any 1-2-3 @function, click the @function selector in the edit line, and choose List All. Next, select the @function you want from the list box, and press F1 (HELP). For information on financial and statistical @function formulas, see Appendix C in the User's Guide.
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 +
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 .XLS file that contains data in rows 8193 to 16384, 1-2-3 inserts a second worksheet and puts the data on it. Formula references to data ranges that subsequently span multiple worksheets 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 8193 or below row 8192.
For information on how 1-2-3 translates row height and column width, see "FORMATTING" above.
