Assist in converting Lotus Macro to Excel?

  • Thread starter Thread starter Mike123
  • Start date Start date
M

Mike123

Hello:

I have a pretty complex macro that I am trying to convert into Excel.
I have tried to convert this Lotus Macro but to work in Excel but just
can't seem to get any luck doing this. I am not sure if this is the
correct area to look for any help in debugging this macro.

I will be happy to post this Macro if anyone would like to take a shot
at converting it into excel?

Thanks in advance for any help.

Mike
 
Mike123 > said:
I have a pretty complex macro that I am trying to convert into Excel.
I have tried to convert this Lotus Macro but to work in Excel but just
can't seem to get any luck doing this. I am not sure if this is the
correct area to look for any help in debugging this macro.

I will be happy to post this Macro if anyone would like to take a shot
at converting it into excel?
Click to expand...
....

I'll take a shot at it. I've done a few of these myself. If there are lots
of {?}, {GET} or {LOOK} calls, a ground-up rewrite would be a better idea.

The best way to display 123 classic macros that use a lot of defined names
is to use the first few character positions on each line for range names,
and start all macro code a few more character positions to the right of
that. And show defined names used as variables. For example,

_CNTR_ = A:A1

\A: {for _CNTR_,0,10,1,\A_SUB}
{return}

\A_SUB: {d}{edit}{home}'~
{return}
 
Here is the Lotus Macro that I cannot convert to Excel.

/XCTILDE~/RSBA~ {WINDOWSOFF}/XCTILDE~/RSBA~ RANGE TO SEARCH
/BA=?/~ ITEM TO SEARCH FOR
BR{CE}
/BA=190/~ REPLACEMENT ITEM
A{HOME} RANGE TO SEARCH :FLL~
/RSDOC1~ ITEM TO SEARCH FOR :FLT~
/BA=?/PROV=/~ :FLB~
BR{CE} REPLACEMENT ITEM
/BA=75/PROV=4090/~ :FLR~
A{HOME} RANGE TO SEARCH :FLT~
/RSDOC2~ ITEM TO SEARCH FOR :FLB~
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM :FLT~
/BA=75/PROV=2640/~ :FLB~
A{HOME} RANGE TO SEARCH
/RSDOC3~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=75/PROV=9380/~ {D 3}
A{HOME} RANGE TO SEARCH {END}{R}
/RSDOC4~ ITEM TO SEARCH FOR /C~{U}~
/BA=?/PROV=/~ {END}{L}
BR{CE} REPLACEMENT ITEM {D}
/BA=75/PROV=910/~ /C{END}
A{HOME} RANGE TO SEARCH {R}{END}
/RSDOC5~ ITEM TO SEARCH FOR {R}{END}
/BA=?/PROV=/~ {R}~{U}~
BR{CE} REPLACEMENT ITEM {U 2}
/BA=75/PROV=2980/~ /M{D}
A{HOME} RANGE TO SEARCH {R 13}~
/RSDOC6~ ITEM TO SEARCH FOR {D}~
/BA=?/PROV=/~ {NS}
BR{CE} REPLACEMENT ITEM
/BA=75/PROV=345/~
A{HOME} RANGE TO SEARCH
/RSDOC7~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
(/BA=99/PROV=9999/)~
A{HOME} RANGE TO SEARCH
/RSDOC8~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
(/BA=99/PROV=9999/)~
A{HOME} RANGE TO SEARCH
/RSDOC9~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
(/BA=99/PROV=9999/)~
A{HOME} RANGE TO SEARCH
/RSDOC10~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
(/BA=99/PROV=9999/)~
A{HOME} RANGE TO SEARCH
/RSDOC11~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
(/BA=99/PROV=9999/)~
A{HOME} RANGE TO SEARCH
/RSDOC12~ ITEM TO SEARCH FOR /CC:AA160...C:AG160~~{NS}
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=9064/~
A{HOME} RANGE TO SEARCH
/RSDOC13~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=8000/~
A{HOME} RANGE TO SEARCH
/RSDOC14~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=9066/~
A{HOME} RANGE TO SEARCH
/RSDOC15~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=9029/~
A{HOME} RANGE TO SEARCH
/RSDOC16~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=8901/~
A{HOME} RANGE TO SEARCH
/RSDOC17~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=1085/~
A{HOME} RANGE TO SEARCH
/RSDOC18~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=4271/~
A{HOME} RANGE TO SEARCH
/RSDOC19~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=4160/~
A{HOME} RANGE TO SEARCH
/RSDOC20~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=3733/~
A{HOME} RANGE TO SEARCH
/RSDOC21~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=2575/~
A{HOME} RANGE TO SEARCH
/RSDOC22~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=2100/~
A{HOME} RANGE TO SEARCH
/RSDOC23~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=3361/~
A{HOME} RANGE TO SEARCH
/RSDOC24~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=3903/~
A{HOME} RANGE TO SEARCH
/RSDOC25~ ITEM TO SEARCH FOR
/BA=?/PROV=/~
BR{CE} REPLACEMENT ITEM
/BA=190/PROV=7865/~
A{HOME} RANGE TO SEARCH
/RSDOC26~
/BA=?/PROV=/~
BR{CE}
/BA=190/PROV=3903/~
A{HOME}
* {goto}F:D112~ ITEM TO SEARCH FOR
{EDIT}{HOME}{R}({END})~{D}
{EDIT}{HOME}{R}({END})~{D} REPLACEMENT ITEM
{EDIT}{HOME}{R}({END})~{D}
{EDIT}{HOME}{R}({END})~{D} RANGE TO SEARCH
{EDIT}{HOME}{R}({END})~{D} ITEM TO SEARCH FOR
{EDIT}{HOME}{R}({END})~{D}
{EDIT}{HOME}{R}({END})~{D} REPLACEMENT ITEM
{EDIT}{HOME}{R}({END})~{D}
{EDIT}{HOME}{R}({END})~{D} RANGE TO SEARCH
{EDIT}{HOME}{R}({END})~{D} ITEM TO SEARCH FOR
{EDIT}{HOME}{R}({END})~{D}
{EDIT}{HOME}{R}({END})~{D} REPLACEMENT ITEM
{EDIT}{HOME}{R}({END})~{D}
{EDIT}{HOME}{R}({END})~{D} RANGE TO SEARCH
{EDIT}{HOME}{R}({END})~{D} ITEM TO SEARCH FOR
{EDIT}{HOME}{R}({END})~{D}
{EDIT}{HOME}{R}({END})~{D} REPLACEMENT ITEM
{EDIT}{HOME}{R}({END})~{D}
{EDIT}{HOME}{R}({END})~{D} RANGE TO SEARCH
{EDIT}{HOME}{R}({END})~{D} ITEM TO SEARCH FOR
{EDIT}{HOME}{R}({END})~{D}
{EDIT}{HOME}{R}({END})~{D} REPLACEMENT ITEM
{EDIT}{HOME}{R}({END})~{D}
{EDIT}{HOME}{R}({END})~{D} RANGE TO SEARCH
{EDIT}{HOME}{R}({END})~{D} ITEM TO SEARCH FOR
{EDIT}{HOME}{R}({END})~{D}
{GOTO}c:d150~/RE{END}{DOWN}~
{GOTO}AD:d1~/RE{END}{DOWN}~ REPLACEMENT ITEM
{GOTO}B:A1~
{GOTO}C:A1~
{GOTO}A:A1~

Any help would really be appreaciated.

Thanks

Mike
 
Mike123 > said:
Here is the Lotus Macro that I cannot convert to Excel.
Click to expand...

Looks like you pasted in several columns from your 123 file. The macro in
the second column looks like it'd be equivalent to


Sub foobar()
Application.ScreenUpdating = False

Call TILDE

Names("BA").RefersToRange.Replace What:="/BA=?/", _
Replacement:="/BA=190/", LookAt:=xlPart
Application.Goto Reference:=Names("BA").RefersToRange, Scroll:=True

Names("DOC1").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=75/PROV=4090/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC1").RefersToRange, Scroll:=True

Names("DOC2").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=75/PROV=2640/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC2").RefersToRange, Scroll:=True

Names("DOC3").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=75/PROV=9380/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC3").RefersToRange, Scroll:=True

Names("DOC4").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=75/PROV=910/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC4").RefersToRange, Scroll:=True

Names("DOC5").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=75/PROV=2980/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC5").RefersToRange, Scroll:=True

Names("DOC6").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=75/PROV=345/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC6").RefersToRange, Scroll:=True

Names("DOC7").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="(/BA=99/PROV=9999/)", LookAt:=xlPart
Application.Goto Reference:=Names("DOC7").RefersToRange, Scroll:=True

Names("DOC8").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="(/BA=99/PROV=9999/)", LookAt:=xlPart
Application.Goto Reference:=Names("DOC8").RefersToRange, Scroll:=True

Names("DOC9").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="(/BA=99/PROV=9999/)", LookAt:=xlPart
Application.Goto Reference:=Names("DOC9").RefersToRange, Scroll:=True

Names("DOC10").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="(/BA=99/PROV=9999/)", LookAt:=xlPart
Application.Goto Reference:=Names("DOC10").RefersToRange, Scroll:=True

Names("DOC11").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="(/BA=99/PROV=9999/)", LookAt:=xlPart
Application.Goto Reference:=Names("DOC11").RefersToRange, Scroll:=True

Names("DOC12").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=9064/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC12").RefersToRange, Scroll:=True

Names("DOC13").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=8000/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC13").RefersToRange, Scroll:=True

Names("DOC14").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=9066/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC14").RefersToRange, Scroll:=True

Names("DOC15").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=9029/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC15").RefersToRange, Scroll:=True

Names("DOC16").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=8901/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC16").RefersToRange, Scroll:=True

Names("DOC17").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=1085/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC17").RefersToRange, Scroll:=True

Names("DOC18").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=4271/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC18").RefersToRange, Scroll:=True

Names("DOC19").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=4160/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC19").RefersToRange, Scroll:=True

Names("DOC20").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=3733/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC20").RefersToRange, Scroll:=True

Names("DOC21").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=2575/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC21").RefersToRange, Scroll:=True

Names("DOC22").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=2100/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC22").RefersToRange, Scroll:=True

Names("DOC23").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=3361/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC23").RefersToRange, Scroll:=True

Names("DOC24").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=3903/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC24").RefersToRange, Scroll:=True

Names("DOC25").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=7865/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC25").RefersToRange, Scroll:=True

Names("DOC26").RefersToRange.Replace What:="/BA=?/PROV=/", _
Replacement:="/BA=190/PROV=3903/", LookAt:=xlPart
Application.Goto Reference:=Names("DOC26").RefersToRange, Scroll:=True

Application.Goto Reference:=Worksheets("F").Range("D112"), Scroll:=True
For i = 0 To 25
ActiveCell.Offset(i, 0).Formula = _
"(" & ActiveCell.Offset(i, 0).Formula & ")"
Next i

Application.Goto Reference:=Worksheets("C").Range("D150"), Scroll:=True
Range(ActiveCell, ActiveCell.End(xlDown)).ClearContents

Application.Goto Reference:=Worksheets("AD").Range("D1"), Scroll:=True
Range(ActiveCell, ActiveCell.End(xlDown)).ClearContents

Application.Goto Reference:=Worksheets("C").Range("A1"), Scroll:=True
Application.Goto Reference:=Worksheets("B").Range("A1"), Scroll:=True
Application.Goto Reference:=Worksheets("A").Range("A1"), Scroll:=True

Application.ScreenUpdating = True
End Sub


Note that /XCTILDE~ is equivalent to {TILDE}, which is a call to a macro
subroutine named TILDE.
 
Back
Top