| 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" |
|
@@ |
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 |
|
|
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"
|
