Lotus Conversion Check List

This is part of the check list we use when converting Lotus 123 files into Excel.

In itself it's not comprehensive, not all apply in all cases, but could serve as a guide if you are doing your own conversions.

Remember the golden rule when converting files:
"Do not use the original - take a copy first"

 
Check out "Excel for Lotus 123 Users" on the "Examples" page
 

@@

Change to INDIRECT(cellref)

Alignments

Check And Correct

Buttons

Reinstate and assign to macros

Column Widths & Row Heights

Check & Correct

Comments Added

Set Tools, Options, View, Comments To "Comments Indicator Only"

Current Filename

Check and remove before range names in any formulae

Data Files

Obtain all external source files Eg: Txt, Csv, Prn

Data Input Macro

Check with user

Database Functions

Criteria definitions

Date Sequence

Dates in lists must be in a logical order:
Dd/Mm & Mm/Dd Format Problem

Dates

Change 2 To 4 digit years

Embedded Control Codes

Reformat

External File Refs

Check & Correct

Failed Saving As Xlw

Check all whitespace removed.

Fonts

Check Swiss & Dutch Changed To Arial

Formatting

Check currency symbol $ To

Gridlines

Remove cell fill from white to nil

Headers And Footers

Check & Correct

Hidden Rows, Columns And Cells

Check same in Excel As Lotus

Illegal Range Name Characters

Replace with "_"

Input Macros

Excel visits cells in same order as in Lotus

Lotus Macro Control Codes

Delete from named ranges

Macro Button Text

Be brief or use old Lotus control letter(s) in Excel

Paper Size

Letter To A4

Protection

Cell settings same as Lotus and do not affect macros

Range Names Illegal 1st Char

Prefix with Zz_

Whitespace

Check all removed

 
 
Other
Not all Lotus range names convert without problem.
Those that won't convert include:
  • Include spaces
  • Begin with a number
  • Contain characters illegal in Excel, e.g., '' '&!$%^*()+=-{}[]@~~<>,/ (and the SPACE character)
Lotus Macros
The old spreadsheet macros (those written before Lotus Script) can be notoriously difficult to convert especially if you can distinguish macro commands from range names and cell addresses. It is worth spending time making the range names stand out e.g:

"/fsfilename.wk1" becomes a lot clearer as "/fsFILENAME.WK1"

We use our own tools to automate this process which can save a considerable amount of time when trying to understand what the original author was trying to achieve. Our current lookup list of macro commands is over 1000 rows of data. Here's an extract:

/c

Copy

/dd

Data Distribution

/dmi

Data Matrix Invert

/dmm

Data Matrix Multiply

/dp

Data Parse

/dq

Data Query

/dqc

Data Query Criteria

/dqd

Data Query Delete

/dqe

Data Query Extract

/dqf

Data Query Find

/dqi

Data Query Input

/dqo

Data Query Output

/dqr

Data Query Reset

/dqu

Data Query Unique

/dr

Data Regression

/dsd

Data Sort Data-Range

/dsg

Data Sort Go

/dsr

Data Sort Reset

/dsrd

Data Sort Reset Data-Range

/dsrg

Data Sort Reset Go

 
There are tools that might help you convert Lotus 123 macros to Excel VBA, but  nothing (AFAIK) that does everything.

In many of the companies I've helped to convert from 123 to Excel we've also taken the opportunity to review why files still exist; that is to say, we've asked if they are really necessary and do they really need conversion. In one department the number of files for conversion came down from several hundred to four!

One area you might review are "print macros". Lotus users needed print macros because, in earlier versions, they were restricted to defining only one print area. Many users carried this on even when multiple definitions became available. In Excel you can have different print settings for each sheet plus those you set up in custom views. Immediately most print macros become redundant.

Macros do take a long time to convert. We have found that it takes, on average, half a day per menu item. Some menu items can be converted in a matter of minutes, but others can take days. It all depends on how complex the macros are.

One other thing you should watch out for is what we call "White space". A Lotus file of, say, 50k converts into an Excel file of many megabytes. This is to do with the way Excel reads some Lotus' row and column formats. For example, a Lotus column, which in older versions consisted of 8192 rows, has to translate into an Excel column of 65536 rows. Excel doesn't take the Lotus column format and apply it to the Excel column, it applies an individual setting for each cell, hence the mega increase in size. You might find that buying the latest copy of Lotus 123 will help you as it will have the latest 123 to Excel file converters. Excel also has basic file converters, but with some files the 123 version can be better.

 
Four 123 macro statements you may sorely miss: {?}, {abs}, {look} and {get}.
If you have any macros that depend on these, conversion won't be easy. There are only very approximate VBA ways of simulating {?} and {abs}. {look} and {get} can be done using Windows API calls, but not from within VBA proper.
  • {?} suspends macro execution allowing free interaction
  • {LOOK location} Type ahead buffer
  • {GET location} suspends macro execution until you press a key, then places the keystroke pressed in "location"

Originally published: 2001
Last modified 13-Mar-2011