Category : Lotus and other Spreadsheets
Archive   : LTSTIPS3.ZIP
Filename : SUBDIRS.123

Automatic 1-2-3 Subdirectories
(PC World January 1985 Star-Dot-Star)

A problem arises with 123 when using a hard disk with worksheet
files located in different subdirectories. It's inconvenient to issue
commands to change the default directory each time you use the program,
but a batch file will do the same thing.
Suppose that 123 is stored in the subdirectory \LOTUS with
worksheet files in the subdirectories \WP, \WP\BUDGET, and
\PRODUCT\FORECAST. First copy the file 123.CNF to 123X.CNF and then
run 123. Issue the command /Worksheet Global Default Directory. Exit
123 and copy the file 123.CNF to a file whose name reflects the
subdirectory name you just entered but ends with .CNF, such as
BUDGET.CNF. Repeat these steps for each subdirectory, as needed.
Rename the file LOTUS.COM as LOTUSX.COM. Then create the LOTUS.BAT
file (below). To use the batch file, type LOTUS followed by the name
you assigned to the .CNF file that corresponds to the subdirectory you
desire, such as LOTUS BUDGET. If you don't specify a subdirectory, the
original default directory in 123X.CNF will be used.

LOTUS.BAT: ECHO OFF
COPY %1.CNF 123.CNF >NUL
LOTUSX
COPY 123X.CNF 123.CNF >NUL

-----------------------------------------------------------------
1-2-3 Subdirectory Menu
(PC World July 1985 Star-Dot-Star)

The 123 worksheet in DIRMENU.WKS provides an alternative to the
method given for setting the LOTUS default directory in "Automatic
1-2-3 Subdirectories" (*.*, January 1985) (above). DIRMENU dispenses
with the task of creating and saving separate copies of the 123.CNF
file for each subdirectory. It also eliminates the file copying that
takes place every time LOTUS is loaded under the previously given
method. Instead, only one small worksheet needs to be kept in the 123
subdirectory. Follow these steps to create the worksheet:
1. Start 123 and set the /Worksheet Global Default Directory to
the drive and directory from which 123 boots, then Update and Quit from
this menu.
2. Type a list of the subdirectories you will want to access in
column A, one per cell. Use /Worksheet Column-Width Set to adjust for
long directory names.
3. Type the macro command labels as shown in cells B1, B2, and B4.
The empty B3 cell will automatically fill in when the menu is used.
4. Now use /Range Name Create to name the macro \0 (the
autoexecute macro) and assign it the cell range B1..B4. Use /Range
Name Create again to name \R, another keyboard macro that references
the same cell range.
5. Finally, use /File Save to store this as the automatic-loading
worksheet by giving it the name AUTO123.
Now whenever 123 is started, this worksheet will appear on screen
and act like a pull-down menu. You simply point with the cursor to the
desired directory and press Enter. If you select an invalid directory,
and error message to that effect will appear. You can then press Esc
and use Alt-R (Restart) to pick another directory from the menu.
To add, delete, or edit directories in the menu, simply load 123,
press Ctrl-Break, and make the necessary changes. You can use the Data
Sort command to keep the list in column A neatly sorted. When done
making changes, issue the /File Save Replace command before pressing
Alt-R to test the new menu.

A
1 A:\ {Home}{?}/C~B3~
2 B:\ /WGDD{Esc}
3 C:\
4 C:\LOTUS ~Q/WEY
5 C:\LOTUS\BALANCE
6 C:\LOTUS\DATABASE
7 C:\LOTUS\DBASE2
8 C:\LOTUS\DBASE2\IMPORTS
9 C:\LOTUS\INCOME
10 C:\LOTUS\MISC
11 C:\LOTUS\WKS
12 etc.

-----------------------------------------------------------------
Subdirectory Menus
(PC Magazine Vol 4 No 13 June 25, 1985 Spreadsheet Clinic)

There is a relatively simple way using menu macros to choose
subdirectories from 123 at the touch of a button. This is a great
convenience for hard disk users who keep different 123 files in
different subdirectories. If the file containing the menu macros is
called AUTO123.WKS, and the initial macro is \0, you get a menu of
options as soon as you enter 123.
The macro below contains explanations in column A. The long
prompts in lines 31 and 42 have been shortened to fit the page, but
they can be as long as will fit on the second row of the menu. The
first menu lets you retrieve files from any of six different directories
or, in column H, gives you the option of creating a new file for any of
those six subdirectories. In either case, the macro changes 123's
default directory according to the menu selection, and you end up with
an old file to work with (line 34) or a blank screen in which to create
a new one in (line 45). Since 123 menus allow no more than eight
choices, this macro in its simplest form lets you choose from only
eight subdirectories. But, if you name the last menu selection "More"
you can use it to call up another menu of subdirectories.
- - - - -
Figure: A menu macro to choose subdirectories from 123.

21 A B C
22
23 MACRO \0 -------------> /xmMENU~
24 AUTOMATICALLY CALLS UP
25 THE MENU CALLED "MENU"
26 WHICH IS IN RANGE
27 B30 TO H34
28
29 MENU:
30 SHORT PROMPT ---------> C: A: Accounting Marketing Personal Bills New File
31 LONG PROMPT ---------> Root Floppy EXPLANATION EXPLANATION EXPLANATION EXPLANATION Start a New File
32 FILE DIRECTORY COMMAND -> /FD /FD /FD /FD /FD /FD /xmmenu2~
33 NAME OF CORRESP. DIRECTORY -> C:\ A:\ C:\accntg C:\market C:\personal C:\bill
34 ALLOW USER TO RETRIEVE FILE -> ~/FR ~/FR ~/FR ~/FR ~/FR ~/FR
35 FROM DIRECTORY WITH
36 FILE RETRIEVE COMMAND
37
38 THIS IS MENU 2 (RANGE B41 TO G45)
39 WHICH IS CALLED BY New File
40 IT'S FOR CREATING NEW FILES
41 SHORT PROMPT ---------> C: A: Accounting Marketing Personal Bills
42 LONG PROMPT ---------> Root Floppy EXPLANATION EXPLANATION EXPLANATION EXPLANATION
43 FILE DIRECTORY COMMAND -> /FD /FD /FD /FD /FD /FD
44 NAME OF CORRESP. DIRECTORY -> C:\ A:\ C:\accntg C:\market C:\personal C:\bill
45 ERASES WORKSHEET ------> ~/wey ~/wey ~/wey ~/wey ~/wey ~/wey
46 SO USER CAN CREATE HIS OWN


-----------------------------------------------------------------
Hard Disk Menu
(PC Magazine Vol 4 No 15 July 23, 1985 Spreadsheet Clinic)

When you use 123 with a hard disk, it's convenient to keep
different kinds of files in different subdirectories. The macro-based
menu system shown in Figures 1 and 2 is easy to use and lets you switch
directories and retrieve commonly used files. If the menu system is
put into a file named AUTO123.WKS, it will load automatically when you
start up and display a menu (Figure 1). If you give cell J8 the range
name \0 the macro will also start up immediately. It will send the
cursor to cell E16 (the data entry cell), prompt you to make a
selection, and then execute your choice.
The key to the macro is the series of IF statements in Figure 2.
These cause the macro to branch to the different choices in cells J20
to J36. These choices must, of course, match the menu choices in
Figure 1, but you can modify all of them to suit the needs of your
system. You are limited only by your imagination and by what 123
allows. If you keep a copy of this file in all of your subdirectories,
you can use it whenever you need to switch files or change drives.
Also, even after you have made a choice from the menu, you can cause
the macro to re-execute by hitting Alt-A. This reads the file in from
disk once more and \0 autoexecutes all over again.
Editor's Note: This is an interesting alternative to macros that
do the same thing by setting up menus with the /XM command (Vol 4 No 13
above). With the /XM command, you can choose from no more than eight
options, but this method lets you choose from as many options as you
can cram into one screen. Also, since you're not dealing with the
control panel, you have greater freedom in setting up text to describe
your menu choices. The branches in Figure 2 (J20..J36) are double-
spaced so that each macro branch will end without running on to the
branch below. You can save space by adding a /XQ to the end of each
branch and removing the blank lines. /XQ will halt the macro. Just
remember to renumber the XG commands in cells J10 to J17. Also, the
Alt-A macro, which causes the whole thing to re-execute, can be
eliminated. It's there only because you can't restart an autoexecute
macro by hitting Alt-0. You can, however, give cell J8, which starts
the autoexec macro, a second range name (say, \R for repeat). The
macro will still autoexecute, and by giving J8 another range name
you can restart the autoexec macro just as you would any other macro,
by hitting Alt-R. Re-execution is much quicker because you don't have
to read the file in from disk again in order to restart the macro.
- - - - -
Figure 1: This screen will appear when you start 123.

CURRENT 1-2-3 FILES

1 Personal bank account
2 Company bank account
3 Investment portfolio
4 Software list
5 CHANGE DIRECTORY to B:
6 CHANGE DIRECTORY to C:\PERSONAL
7 CHANGE DIRECTORY to D:\BUSINESS
8 / File Retrieve - normal FILE RETRIEVE this subdirectory
9 LEAVE THIS MENU PROGRAM, ERASE SHEET

Enter Number of File to Use 5
*********

To retrieve this AUTO123 command file, press Alt-A
- - - - -
Figure 2: This macro selects hard disk directories and retrieves
files.

I J K
-------------------------------------------------------------
4
5
6 "\A" /FRAUTO123~
7
8 "\0" {GoTo}E16~{?}~ ' Cursor to E16, accept input.
9 /XIE16=1~/XGJ20~ ' Series of IF statements
10 /XIE16=2~/XGJ22~ ' that cause
11 /XIE16=3~/XGJ24~ ' macro to branch
12 /XIE16=4~/XGJ26~ ' according to number
13 /XIE16=5~/XGJ28~ ' input by
14 /XIE16=6~/XGJ30~ ' the user.
15 /XIE16=7~/XGJ32~ ' Branches are
16 /XIE16=8~/XGJ34~ ' numbered below.
17 /XIE16=9~/XGJ36~ '
18
19
20 1 /FDB:\PERSONAL~/FRbankac~
21
22 2 /FDB:\BUSINESS~/FRcobank~
23
24 3 /FDB:\PERSONAL~/FRinvest~
25
26 4 /FDB:\BUSINESS~/FRsoftwr~
27
28 5 /FDB:\~/FR
29
30 6 /FDB:\PERSONAL~~
31
32 7 /FDB:\BUSINESS~~
33
34 8 /FR
35
36 9 /WEY

-----------------------------------------------------------------
Macro Choices
(PC Magazine Vol 4 No 22 October 29, 1985 Spreadsheet Clinic)
(from article entitled Printer Setup Files)

In response to the Hard Disk Menu item (Vol 4 No 15 July 23, 1985)
it was noted that /XM macro menus can offer no more than eight choices.
This is true; however, for the last option in one menu you can offer
the user another menu. To let the user scroll through all his choices
more freely, you can even make the last item in the last menu point
back to the first menu, and the first item in the first menu point to
the last menu.