1123 to Excel

[LonnieP]

?Gentlemen: I have a spreadsheet SYSTEM (for structural engineering) I would like to
transfer from Lotus to Excel. Excel seems to translate the sheet and modules to the
point where they work correctly. The issue is getting the macros translated to Excel
macros or VBL. Without the macros the system is cumbersome to use, but still useful.
The Excel macros are where I need help, lots of it probably.

I propose to post my Lotus base sheet and a group of modules for use by anyone who
has Lotus 123 Release 5, AND, the same sheet and modules translated into Excel
(2003) for all of us to work on and get the macros running correctly. I can pretty much
guarantee you’ll find this approach to spreadsheet use intriguing and stimulating.
Additionally, the work on the macros, if done in the open on this web site, should be a
learning experience for anyone (like me) who wants to know more about, or improve
their skill with, Excel macros and VBL. Who knows, maybe even MS or IBM might look
in once in a while to see what real live working engineers do with their products.

If this is something that would interest you, please post a response to this message.

LonnieP

 

[IDS]

Lonnie - I went through a similar process a few years ago. I'd certainly be interested in looking at your work. You might find some of the stuff on my blog helpful (see link below). There's nothing directly about Lotus script or macros, but quite a lot about efficient ways to get data from the spreadsheet into the macro.

It's VBA by the way (Visual Basic for Applications)

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[LonnieP]

Thanks for the reply Doug. I guess I got brain fade on the VBL vs VBA thing, and a slow pinky finger on the "1's" in the title.

Don't know how much structural work you do but the basic concept should be applicable to almost any kind of engineering where spreadsheets are used. I'm structural, so that's were I started. Do you have access to Lotus 123 Release 5?

LonnieP

 

[IDS]

I do structural as well, mainly bridges and buried structures.

I have 123 Rel 9.5 installed, but it runs most Rel 5 macros without any problem.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

You might find some spreadsheets from my blog useful; particularly the one that returns the properties of selected shapes; and one with a list of shapes with an example of each:

http://newtonexcelbach.wordpress.com/?s=shape+properties

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[LonnieP]

For those who still have 123 R5, here is a zip file containing a set smarticons that goes in the "123R5W\programs\sheetico" directory. They are accessed by going to the smarticon tools and choosing the "JNCALC" icon set. Lotus/IBM changed the format for the smarticons in Release 9.X so these need to be redone for 9.X. In any case, they pretty much do the same thing 9.X and Excel do when you right click on a cell (Change font color, fix decimal places, make/delete page breaks, stuff like that). I'll send the 123 and Excel base sheet next time.

LonnieP

 

[LonnieP]

?Doug and all,

The attached ZIP file contains the lookup files, some support files and the base calc
sheet that hold the modules and has the macros that make the system work. Please
acknowledge that you were able to un-ZIP the file.

Open the calc file (Calc2r5.wk4) in Lotus, print it per the instructions, construct the
directory structure described on your hard drive, then save the file in the J&Ncalc\Pool
directory. In case you are concerned, Lotus doesn’t support macro virus’. Put the
following files in the wood directory: DFL05SHP.WK4, GLBSHAPE.WK4,
LAMSHAPE.WK4, TJISHAPE.WK4 and SP05SHP.WK4. Put AISCSHPS.WK4 in the
steel directory. Everything else goes in the Pool directory. The file BEAMDIAG.WK4
contains the loading diagrams for the beam modules I’ll send later this week. The
building departments seem to feel warm and fuzzy once they see the BEAMDIAG file.

As I indicated, I intend to convert this system over to Excel, with the help of the forum
users. I realized it’s best to have a working copy to reverse engineer from, so that’s why
I’m sending the Lotus system first. I’ll send the beam modules once you tell me you
were able to un-Zip these files. If all works well you should have a working system
before the weekend. I’ll also send TUTOR.WK4 which will explain how to use the
system, except for the R5 smart icons which won’t work on R9.X.

LonnieP

 

[LonnieP]

OOPS! forgot to attach the ZIP file

 

[LonnieP]

The Beamdiag.wk4 file doesn't work without this CGM file in the same directory. Sorry.

 

[LonnieP]

?Doug and all,

Everything in this Zip file goes into the J&Ncalc\Pool directory. The JNCALC2.ZIP file
(and the previously send JNCALC.ZIP) contains everything you need to begin using the
modular spread sheet system to design 90% of the beams, joists, rafters and girders
you're likely to find in your practice. It runs in Lotus Release 5 with only the smart icons
non-functional in Lotus Release 9.x. There are three RUNME.WK4 files in the zip, run
them in order. The last one, RUNME3.WK4, is a tutorial that will train you on the basics
of the system and has a "B" tab (second page) that shows the result of a properly done
Tutorial session.

As most of you use Excel, I suggest you install and run the Lotus version to experience how
the system works before starting to reverse engineer it into Excel. In the next few weeks
I'll be sending the same modules in Excel 2003 (*.XLS). I've already converted some of
them and they seem to work just fine, except for the macros. I'm starting to learn VBA
but I'll need help with those. Hopefully we can all help each other and in the process you can learn how
and why this spread sheet system works.

The system, as written, is in pounds, kips, feet and inches. It's written in such a way
that the beam analysis modules only need the text changed to kilos, meters,
centimeters, etc, (obviously learned my physics in the old metric system circa 1960's)
without any change to the formulas, as they are straight forward statics and not subject
to code changes. The member stress modules are where the work needs to be done
with respect to code changes (as they occur) and measurement systems. It's also easy
to have several LRFD and ASD member stress modules run off the same beam
analysis module for comparison purposes. This capability should pretty much put the
LRFD vs ASD issue to rest. You can have both at the same time AND have your LRFD
load factors vary depending on the demands of each individual member, if you so
choose. AND Yes, I've got a lateral analysis system for light framed and masonry,
concrete tilt-up structures too.

Lastly, none of the formulas or macros are hidden or protected, so you can look at
everything and satisfy yourself that you know what the spread sheet is doing, and how.

Enjoy!

LonnieP