Converting Lotus .WK4 files to Excel format

[Carl Rapson]

I hope this is the right group for this question. Sorry
for the length of this post, but I want to be sure to
explain the problem accurately.

I've been tasked with porting some existing Lotus 1-2-3
files (.WK4) to Excel. Some of the spreadsheets are quite
large, they are all cross-linked with each other, and they
all contain a lot of macros. I understand that I am going
to have to re-write the macros in VBA, but that's not my
problem. What I'm wondering is, which is better for doing
the conversion -- open with 1-2-3 and save as an Excel
file, or open with Excel and save as an Excel file?

I've tried both ways, and each seems to have its
particular problems. For example, if I open the files with
1-2-3 and then save them in .XLS format, I seem to get
most cells (and formulas) converted correctly (except
DSUM, but that's a problem for another time). However,
when I attempt to update values from the links to other
files (Edit|Links...|Update Values), Excel crashes on one
particular file (one with a very large number of links)
and on others tells me that there are inadequate resources
to complete the operation.

If I open the .WK4 files with Excel and then save them
in .XLS format, I don't seem to have the problems with
updating the links but I lose a lot of cell values,
particularly ones where there is an error in the cell. For
example, there is a cell in the .WK4 that contains the
formula "@ROUND((AG1900/ERR),5)" (I'm not sure where
the "ERR" comes from, I am still trying to track it down).
Converting the file from 1-2-3 copies this formula
as "=ROUND((AG1900/#REF!),5)", which I can live with
(because it retains the formula), but converting the file
from Excel puts "#VALUE!" in the cell. On some of the
files, this occurs hundreds of times, so going though cell
by cell to fix this will be very tedious.

There are other examples (formatting, for example), but
these seem to be the most troublesome. So, my question is,
which way is better? And, is there something I am
overlooking to make the conversion easier and/or more
compete? In particular, does anyone know anything about
why attempting to update values from linked files would
cause the described problems?

I greatly appreciate and insights on this conversion.

Thanks,

Carl Rapson

 

[Harlan Grove]

I hope this is the right group for this question. . . .

Technically, microsoft.public.excel.123quattro would have been a better
choice for your post, but no matter.

. . . What I'm wondering is, which is better for doing
the conversion -- open with 1-2-3 and save as an Excel
file, or open with Excel and save as an Excel file?

Generally, open in 123 and save as Excel.

I've tried both ways, and each seems to have its
particular problems. For example, if I open the files with
1-2-3 and then save them in .XLS format, I seem to get
most cells (and formulas) converted correctly (except
DSUM, but that's a problem for another time). However,
when I attempt to update values from the links to other
files (Edit|Links...|Update Values), Excel crashes on one
particular file (one with a very large number of links)
and on others tells me that there are inadequate resources
to complete the operation.

Sad but true, 123 does a much better job with links to other files than
Excel does. It seems that Excel caches values from *closed* linked files in
memory, so if you're working with one file at a time, you may use LOTS of
resources.

If I open the .WK4 files with Excel and then save them
in .XLS format, I don't seem to have the problems with
updating the links but I lose a lot of cell values,
particularly ones where there is an error in the cell. For
example, there is a cell in the .WK4 that contains the
formula "@ROUND((AG1900/ERR),5)" (I'm not sure where
the "ERR" comes from, I am still trying to track it down).

This ERR would need to have been generated by 123 rather than Excel. If this
were, say, a reference to cell X999 and someone or some macro had moved
another cell to a range that included X999, this is what 123 would show.

Converting the file from 1-2-3 copies this formula
as "=ROUND((AG1900/#REF!),5)", which I can live with
(because it retains the formula), but converting the file
from Excel puts "#VALUE!" in the cell. On some of the
files, this occurs hundreds of times, so going though cell
by cell to fix this will be very tedious.

....

This is Excel throwing up it's metaphorical hands in disgust at 123. The ERR
token in 123 is what you get when you convert @ERR (or any error generated
by formula) to a value, e.g., via /RV. ERR is also a perfectly legal range
name. In this regard, 123 has really stupid parsing rules (or stupid lack
thereof).

Because I know you'll find working with external reference links much
harder/slower/flaky in Excel than 123, you'll be needing to change how this
works in the long run. Therefore, translating the other formulas as
completely as possible should be the preferred goal. Save in 123 as Excel.

As for external link references, comment out the formulas that contain them.
You could automate this with a classic macro using {contents} with format
117 to read a cell's formula into a different cell (X), use
#NOT#@ISERR(@FIND("<<",X,0)) to check if it contains an external reference,
and if so, {edit}{home}'~ to convert the formula to a label.

 

[Carl Rapson]

Thanks very much for the reply. A few comments/questions are in-line.

Technically, microsoft.public.excel.123quattro would have been a better
choice for your post, but no matter.

I was using this newsgroup from work, where I can only access it via the web
(MSDN site). I didn't see the 123quattro group from there. I do see it in
Outlook Express, however. I'll be sure to use that group in the future.

Generally, open in 123 and save as Excel.

...

Sad but true, 123 does a much better job with links to other files than
Excel does. It seems that Excel caches values from *closed* linked files in
memory, so if you're working with one file at a time, you may use LOTS of
resources.

...

This ERR would need to have been generated by 123 rather than Excel. If this
were, say, a reference to cell X999 and someone or some macro had moved
another cell to a range that included X999, this is what 123 would show.

...

This is Excel throwing up it's metaphorical hands in disgust at 123. The ERR
token in 123 is what you get when you convert @ERR (or any error generated
by formula) to a value, e.g., via /RV. ERR is also a perfectly legal range
name. In this regard, 123 has really stupid parsing rules (or stupid lack
thereof).

Yes, the ERR was generated by 123. As I said, I'm not sure where the errors
came from, but I will track that down. My main concern was to retain the
formulas. Saving from 123 does the trick.

A question about this part:

...
Because I know you'll find working with external reference links much
harder/slower/flaky in Excel than 123, you'll be needing to change how this
works in the long run. Therefore, translating the other formulas as
completely as possible should be the preferred goal. Save in 123 as Excel.

...

As for external link references, comment out the formulas that contain them.
You could automate this with a classic macro using {contents} with format
117 to read a cell's formula into a different cell (X), use
#NOT#@ISERR(@FIND("<<",X,0)) to check if it contains an external reference,
and if so, {edit}{home}'~ to convert the formula to a label.

I don't really understand what you are saying here. It appears that you are
suggesting I locate all cell formulas that contain external file references,
copy them to another cell, then turn the formula into a label. What about
the original formula? And, if such external references are not the way to
go, how do I do the same thing in Excel?

Thanks again,

Carl Rapson

 

[Harlan Grove]

...
...

Yes, the ERR was generated by 123. As I said, I'm not sure where the errors
came from, but I will track that down. My main concern was to retain the
formulas. Saving from 123 does the trick.

...

There are only 4 ways to generate an ERR token in any formula, so start with a
simple formula like +X:Y999.

1. Move (or *cut* and paste) another range onto a range including A1.
2. Delete a group of columns including column Y in worksheet X.
3. Delete a groups of rows including row 999 in worksheet X.
4. Delete a group of worhsheets including worksheet X.

I don't really understand what you are saying here. It appears that you are
suggesting I locate all cell formulas that contain external file references,
copy them to another cell, then turn the formula into a label. What about
the original formula? And, if such external references are not the way to
go, how do I do the same thing in Excel?

No, I'm suggesting you convert these formula to labels IN PLACE. If you have a
formula like

@AVG(<<C:\foo\bar.wk4>>X:Y999..Z:AZ2000)

in cell W:V321, I'm suggesting you convert it to a label by preceding the
formula in W:V321 with a single quote, so that the contents of W:V321 become

'@AVG(<<C:\foo\bar.wk4>>X:Y999..Z:AZ2000)

This would mean manual translation of the formula after you open the file in
Excel. If there are lots of such formulas, then this may not be a good idea.

As for external link references in general, I'll repeat: 123 is far more
efficient than Excel. 123 is no slower than Excel when the linked files are
open, 123 doesn't try to recalculate these references after refreshing them once
when the linked files are closed, and 123 can pull the values from closed files
using dynamically constructed workbook and range addresses inside @@. Excel uses
A LOT of memory when there are lots of external links. 123 doesn't use as much.

A much bigger potential pain in Excel is that it simply won't allow multiple
open files with the same bas filename. If you have a subdirectory structure for
each month of the year, with a file named PLAN.WK4 in each of these
subdirectories, and you have summary workbooks with formulas like

@SUM(<<C:\XYZ\2003\January\PLAN.WK4>>A:B2..A:B20)
@SUM(<<C:\XYZ\2003\February\PLAN.WK4>>A:B2..A:B20)
@SUM(<<C:\XYZ\2003\March\PLAN.WK4>>A:B2..A:B20)

You can have all 3 of these files open in 123. You could have only one of these
three open in Excel.

If you'd always have all linked files open, then Excel shouldn't be caching
values from external link references, so you shouldn't have resource problems
(other than from having all these files in memory). Are you loading one file at
a time into Excel?

I'd need more details from you in order to figure out what the best approach may
be. How many different files are linked into the file containing the most
diverse set of links? How many formulas containing links? Would all these files
be open at the same time? If not, what's in the files that would be closed?

 

[Harlan Grove]

There are only 4 ways to generate an ERR token in any formula, so start
with a simple formula like +X:Y999.

1. Move (or *cut* and paste) another range onto a range including A1.

....

Oops. Make that

1. Move (or *cut* and paste) another range onto a range including X:Y999.