FileControl for 1-2-3 12/30/93 ABOUT FileControl ----------------- FileControl for 1-2-3 is a Lotus 1-2-3 spreadsheet that is designed to help you manage your files. It works with 1-2-3 version 3 for DOS, and with all the Windows versions of 1-2-3. With FileControl, you do not need to leave 1-2-3 to use DOS or a file manager to rename, copy, move or erase files. And, it automatically handles such commands for the format files. In addition to the file management features, FileControl allows you to open, save, and close groups of worksheets. This gives Lotus 1-2-3 users functionality very similar to what Microsoft Excel users have with their work group feature. All these features and more are managed for you by FileControl. A key feature of FileControl is that it allows you to maintain documentation on each of your files, so that you do not have to rely on DOS's eight character filename to tell you what a file contains and what its purpose is. Descriptions of over two hundred characters can be associated with each file. A file can easily be located by using FileControl's search feature that searches not only the file list, but also the descriptions on the files. And if the file is a 1-2-3 spreadsheet, it can be automatically opened into 1-2-3. FileControl does this by maintaining information about your files in a specially designed 1-2-3 spreadsheet called FILECTRL.WK3. This spreadsheet contains a set of macros that do the file management functions. To handle special needs such as renaming or copying files, a support program, named CTL123.EXE is called by the macros. Each file is identified by its file name and type, the directory it is located in, when it was last modified, the size of the file, and by the file's documentation. The following is a typical file list for a directory: +-----------------------------------------------------------------------------+ | C:\WKSFILES\COST Cost Studies | | ACTUAL.WK4 05/26/93 11:57 87180 Actual annual cost data | | PLAN1.WK3 03/16/93 14:49 45016 Moderate expansion case | | PLAN2.WK3 04/15/93 16:38 51379 Expansion case with new competitio| +-----------------------------------------------------------------------------+ The information shown on each file is (as of the last update run) the day and time last modified, the file size, and a comment describing the file. Although the documentation shown above is very short, you can use over 200 characters of documentation on each file. The first 33 characters will be visible on the screen. The rest can be viewed by editing the documentation cell. You can edit and update the documentation anytime you want. To help find text in the documentation, FileControl uses the 1-2-3 search feature that allows you to scan the file names, the documentation or both for matching text. As the above example illustrates, you can also have documentation that describes the purpose of a directory. You will also notice that the cells containing the directory information is lightly shaded. You have the option to shade or color code directories and files to help them standout on the screen. FileControl works for both files that you maintain on your hard drive and on your floppy disks. Of course, maintaining your files on your hard drive makes it easier to retrieve a file when you want it. And, with FileControl, you can easily create directories to store files in. In addition to using FileControl to manage your spreadsheet files, you can also use it to keep track of your other files. For example, it can keep track of PRN files, DOC files, ZIP files, BAT files, and graphics files. However, only spreadsheets can be opened. TO MAXIMIZE THE BENEFIT OF FileControl, DO NOT REMOVE IT FROM MEMORY. --------------------------------------------------------------------- If you issue the commands "/ File Retrieve" and retrieve a file, this removes FILECTRL.Wk3 from memory. Instead, always use the Open hot key or the Open option on FileControl's main menu. This will keep it in memory all the time. This way, you don't have the delay of having to read the file back into memory each time you want to use it. CONDITIONS OF USE ----------------- LICENSE AGREEMENT FileControl is being distributed by Macro Systems as shareware. This means you can copy the unregistered version of FileControl to evaluate it for a 60 day period. Use beyond this period without registering your copy is strictly prohibited. You will need to register your copy of FileControl if you intend to use FileControl beyond the allowed evaluation period. The program can be stored on multiple machines and payment made for only a single copy only as long as the copies on the different machines are not used simultaneously. Under the software license from Macro Systems, registering a copy of FileControl gives you only the right to use the program. It does not give you the right to resale or distribute the program or prepare or distribute a derivative work or modified version of the program. The shareware version of Filecontrol is the complete program. No features have been held back. Please note that the money you pay to a shareware distributor is to cover his distribution costs, and is not for the purchase or registration of a copy of the program. Macro Systems encourages you to make copies of the UNREGISTERED version of FileControl and give them to others to evaluate. Please feel free to upload the UNREGISTERED version of FileControl to your favorite bulletin boards. The files your are allowed to distribute are the original UNREGISTERED files you received. Do not distribute your copy after you have used it, as it contains information about your files, and has been customized to your machine. If you are a shareware distributor you may distribute the UNREGISTERED version of FileControl software as long as you distribute all the files intact and in an unmodified form. Also, you may not charge more than a reasonable distribution fee. REGISTERED VERSIONS OF FILECONTROL MAY NOT BE DISTRIBUTED TO OTHERS, MAY NOT BE DISTRIBUTED AS SHAREWARE, AND MAY NOT BE PUT ON BULLETIN BOARDS. To determine which version you have, install the software, and run the command "ABOUT" from the FileControl menu when you load the spreadsheet. Do this for both the Excel version and the 1-2-3 version of FileControl. COPYRIGHT AND TRADEMARKS The software files that comprise FileControl are copyrighted (c) software of Macro Systems. All rights reserved. It is not in the public domain nor is it freeware. It is the property of Macro Systems. Modifying the program in any way is strictly prohibited. FileControl is a trademark of Macro Systems. 1-2-3 is a trademark of Lotus Development Corporation. Excel is a trademark of Microsoft Corporation. LIABILITY AND WARRANTY This software is distributed "as is" and without warranties. No statement made about this software by distributors, in advertisements, in software reviews, or in the documentation constitutes a warranty. The user must test the program thoroughly. In no event shall Macro Systems, owner of the copyright, or the author of the program be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of this software. Evaluate and use this program at your own risk. There is no guarantee that this software will operate uninterrupted or error free. Any liability will be limited exclusively to product replacement or refund of purchase price. By using FileControl, you agree that the only remedy available to you will be a refund of the purchase price of the FileControl program. EXCEPT AS EXPRESSLY PROVIDED HEREIN, THERE ARE NO OTHER WARRANTIES, EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE WITH RESPECT TO THE SOFTWARE AND ANY ACCOMPANYING WRITTEN MATERIALS. IN NO EVENT WILL MACRO SYSTEMS BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY LOSS OF PROFITS, LOST SAVINGS, OR OTHER INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF YOUR USE OR INABILITY TO USE THE SOFTWARE, EVEN IF MACRO SYSTEMS OR AN AUTHORIZED REPRESENTATIVE HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. This limited warranty gives you specific legal rights. Some states provide other rights, and some states do not allow excluding or limiting implied warranties or limiting liability for incidental or consequential damages. As a result, the above limitations and/or exclusions may not apply to you. Some jurisdictions have statutory consumer protection provisions that may supersede this section of this Agreement. Evaluation, use, distribution, or purchase of this software indicates your agreement with the terms and conditions of the Limited Warranty/Limitation of Remedies and the other terms and conditions in this document. If you do not agree with the terms and conditions, do not use FileControl, and, if applicable, return the copy you purchased. FEEDBACK / TECHNICAL SUPPORT ---------------------------- All feedback, both positive and negative is welcome. I do like my programs to be as error free as possible, and if possible will try to solve the problem you have. If you have problems, PLEASE CHECK THE DOCUMENTATION FIRST. THERE IS A SECTION ON PROBLEMS THAT WILL HELP. If this does not resolve your problem, I may be contacted by mail at: Macro Systems 1008 Lawndale Road Wilmington, DE 19810 or via CompuServe (ID: 72774,416). You may also fax or call 302-475-0142. Please note that faxes will be answered by mail and phone calls will be returned collect only. Also, phone calls can only be returned in the evening (East Coast time), time permitting. THE FILES THAT MAKE UP FileControl ---------------------------------- THERE ARE TWO VERSIONS OF FileControl: One for Lotus 1-2-3, and one for Microsoft Excel. They are being distribted separately. However, if you register for one version, you do not have to register the other version. So you get a two for one deal. However, you will have to download or order the copy you don't have. The files that make up FileControl are listed below. The files are being distributed in compressed format. The naming convention of the ZIP file is: CTRL-Lxx.ZIP for the Lotus 1-2-3 version CTRL-Exx.ZIP for the Excel version where the x's are replaced with the version of FileControl for Lotus 1-2-3 and Excel respectively. Thus you can tell from the title if the version you are using is different from another that may be on your bulletin board. For the 1-2-3 version of FileControl the files are: FILECTRL.WK3 - The 1-2-3 worksheet for FileControl FILECTRL.FM3 - The WYSIWYG format file for the above file AUTO123.WK3 - Auto opening worksheet to open FILECTRL.WK3 CTRL123.EXE - support program called by FILECTRL.WK3 INST123.EXE - INSTALLATION program for 1-2-3 version CTRL123.TXT - ASCII documentation for the Excel version 123DOC.EXE - Compressed documentation in Microsoft Word 2 for Windows for the 1-2-3 version. Also may be read and printed by Ami Pro and WordPerfect. Key points are emphasized and pictures are much better. - CTRL123.DOC created when run. - DISTRIBUTE the EXE file not the DOC file. README.TXT - Order form for FileControl, along with the benefits you get by registering. FIXES.TXT - Documentation of changes made to the program. For the Excel version of FileControl the files are: FILECTRL.XLS - Excel 4 FileControl worksheet FILECTRL.XLA - Excel 4 add-in CTRLEXCL.EXE - Support program called by FILECTRL.XLA INSTEXCL.EXE - INSTALLATION program for Excel version CTRLEXCL.TXT - ASCII documentation for the Excel version EXCELDOC.EXE - Compressed documentation in Microsoft Word 2 for Windows for the Excel version. Also may be read and printed by Ami Pro and WordPerfect. Key points are emphasized and pictures are much better. - CTRLEXCL.DOC created when run. - DISTRIBUTE the EXE file not the DOC file. README.TXT - Order form for FileControl, along with the benefits you get by registering. FIXES.TXT - Documentation of changes made to the program. HOW TO INSTALL -------------- The install program installs these programs: - A 1-2-3 worksheet called FILECTRL.WK3 - A 1-2-3 worksheet called AUTO123.WK3 (Installed only if one is not already present) - A support program file called CTL123.EXE Both FILECTRL.WK3 and AUTO123.WK3 should be installed in your Lotus 1-2-3 default directory. One of the items you need to know is the name and path of this default directory. To find out which directory this is, load 1-2-3 and issue the commands "/*Worksheet*Global*Default*Directory". The directory that appears is the default directory, along with its path. NOTE DOWN THE FULL PATH AND DIRECTORY NAME. If the default directory is a floppy disk, you should create a directory on your hard drive to store FILECTRL.WK3 and AUTO123.WK3, and make it your default directory. Do this before running INST123. This makes using the FILECTRL.WK3 easier. Do this by: - First get to a DOS prompt first. If you are still in 1-2-3, issue the commands "/*System" to go to a DOS prompt. - If this is the drive you want the new directory in, go on to the next step. Otherwise, type the letter of the desired drive for the new directory and a colon (example: C:) and press enter. This changes you to that directory. - Type "CD\". This puts you at the root or first directory in this drive. - Create a new directory by typing "MD ZZZ", where "ZZZ" is the name (up to 8 characters) of the desired directory. For example, "MD WKSFILES". - Confirm you created the new directory by typing "CD ZZZ", where "ZZZ" is the name of the desired directory. Your prompt should display the name of the new directory. - If you went to the DOS prompt by typing "/*System", type "EXIT" to return back to 1-2-3 - After you have created the directory, go into 1-2-3 and issue the commands "/*Worksheet*Global*Default*Directory", and replace the directory and path that appears with the path and name of the one you created. - Select the command "Update" to make this change permanent. (The full 1-2-3 commands are "/*Worksheet*Global*Default* Update".) Another item you will need to know is the path and name of a directory that is in your path statement when you run 1-2-3. To find this out: - go into 1-2-3 and issue the commands "/ System", - Type the command "PATH" and press enter. You can USE ANY OF THE DIRECTORIES THAT APPEAR. - Note down both the directory name and path of the one you want to use. The recommended directory is your main directory, as it is almost always in your path whenever you run 1-2-3. For example "C:\" or "D:\". - Type the command "EXIT" and press enter to return to 1-2-3. If you have different versions of 1-2-3 on your computer, repeat the above process to insure the directory is good for all versions. If necessary you can run the install program and install multiple copies of CTL123.EXE. However, this is not recommended. To avoid doing this, get help to modify your path statements so a common directory is accessible from all versions of 1-2-3. After you have the above information, run INST123 by: - Typing A:INST123 (if "A" is not the floppy drive containing the installation disk, enter its letter instead, or the path where the file is located) and press enter. - If you are running Windows, type the above in the RUN box of the Program Manager, or double click on INSTL123.EXE from within the File Manager. - If you have copied the files to a directory on your hard drive, you can run INST123.EXE by going to that directory and typing INST123. The install program will prompt you for the information it needs and then install the files as discussed above. THE 1ST TIME YOU RETRIEVE FILECTRL.WK3 -------------------------------------- If the AUTO123 file was copied to the default directory by the install program, the worksheet FILECTRL.WK3 will load automatically when 1-2-3 is loaded. If you did not install the AUTO123.WK3 file, you will need to retrieve FILECTRL.WK3 manually when you go into 1-2-3. If you already have an AUTO123 spreadsheet, you should add the commands "/frFILECTRL.WK3~" to the end of your "\0" macro to automatically retrieve the FILECTRL.WK3 spreadsheet. When you retrieve FILECTRL.WK3 for the first time, a macro will automatically run. Then you are presented with a screen that looks like the following (if running in Windows; in release 3, the word "CTL" will be "ALT"instead): +-----------------------------------------------------------------------+ | CTL-M CTL-O - OPEN A FILE CTL-N - NEXT SECTION | | MENU CTL-C - CREATE A FILE CTL-P - PREVIOUS SECTION | | CTL-G - GO TO A FILE CTL-S - TEXT SEARCH | | | | DIRECTORY: ENTER PATH AND DIRECTORY HERE | | FILETYPE: WK* 1 <- Do Sub-directories (1=Yes, 0=N | | LAST UPDATED: 1 <- Display empty directories (1=Y | | DESCRIPTION: | +-----------------------------------------------------------------------+ If you are running 1-2-3 release 3, the only difference is at the top: instead of "CTL", "ATL" will appear. The "ALT + a letter" or the "CTL + a letter" represent hot keys that do the activities displayed beside the hot key. The menus and hot keys are discussed in detail later in the manual. The block of rows beginning with the words "Directory" is called a section. Sections are used to group related files. The spreadsheet comes with several such sections for your use. And, you can add sections as you need them. Sections are also discussed in detail later. One of the first things you should do is to check your installation of CTL123.EXE. Do the following to do this check: - Press ALT-M if running release 3, or CTL-M if in Windows - Select Menu 2 - Select ABOUT. - The screen should blank and you should get a response like: "CTL123.EXE Release xxx" - When you return, to 1-2-3, you are placed at the order form location. - This would be a good time to printout the form and register your copy FileControl!. - To return to the top, press the home key. If you get a response like "CTRL123 was not found", then CTL123.EXE was not installed in an acceptable directory, and it should be re-installed. EXERCISES TO ILLUSTRATE KEY FEATURES ------------------------------------ The following is a set of small exercises that will illustrate some of the key features of FileControl: The first exercise illustrates how to use the UPDATE option. This is the option that creates a list of the files that are found in a specified directory and its sub directories. In the cell with the label "Enter Directory and Path here", enter the default directory and path for your 1-2-3 spreadsheets. For example if your default directory is "WKSFILES", and it is located on the C-drive, you would enter "C:\WKSFILES". Many 1-2-3 users have not changed the default directory, and it will be the same directory where the 1-2-3 program files are stored. If you don't have any files stored in the default directory other than the FILECTRL.WK3 and AUTO123.WK3, then you can do one of several things to run the following exercises: - If you have files in a different directory on your hard drive, enter that path and directory instead. - If all your files are on floppies, enter the letter of your floppy drive and put a disk containing spreadsheet files in that drive. Now, with the cell pointer anywhere in the section heading, bring up the main menu by pressing ALT-M if in 1-2-3 release 3, or CTL-M if in 1-2-3 for Windows. At this time, choose the option labeled "Menu 2". Then choose first option on menu 2 is called "UPDATE". The message bar will change to the words "VERIFYING INFORMATION" and then a small menu will appear asking you to confirm your update request. Do so by highlighting the word "YES" and pressing enter. At this point, you will see a series of informational messages go across the screen. At one point, the screen will turn blank and then the informaton about registering your copy appears. If this does not happen, then the CTRL123.EXE file was not installed in a directory that is in the path when 1-2-3 runs. You will then need to re-install the CTRL123EXE file, except to a directory per the installation instrutions. After you press enter on the panel about registering your copy of FileControl, a file search takes place. This is the CTRL123 program running. The results of the file search will scroll across the screen. Once this is done, control is returned to the spreadsheet and the update work continues. When the update is complete, you will see a message bar that says "UPDATE COMPLETE, PRESS ENTER TO SEE RESULTS". When you do so, you will see a list of the files (of the file type listed in the section heading) that are in the directory. If the directory has sub-directories, you will see them listed, along with the files that they contain. Beside each file will be the date and time the file was last modified, and the size of the file. Run an UPDATE any time you want to update the information on the files or the file list. At this stage, none of your files are documented. Move the cursor to one of the file rows, and place it in column "F". Type in some documentation. As much as you want (up to about 240 characters). Now do this on several more files. One thing you might want to do is to enter two spaces at the beginning of each line of documentation. This improves readability. (Whenever an update is run, the two spaces are automatically added for you.) Let's say you come across a file that you don't recall what it was for, so you don't know what documentation to enter. No problem. Place the cell pointer anywhere on that file's row, and press ALT-O (Release 3) or CTL-O (Windows). This will open the file. Examine it and figure out what it is. Once you are done, press ALT-M or CTL-M. This will return you to FILECTRL.WK3 so you can enter the documentation. But what to do about the file you just opened? Press ALT-M or CTL-M again, and a menu will appear. Select the option X-MEMORY. A screen will appear showing the files in memory and asking you which one to delete. Highlight the file you opened and press enter. The file is removed from memory (but not from the disk). You don't have to return to FILECTRL.WK3 to remove a file from memory. You can do the same thing by pressing CTL-X (ALT-X in release 3). Use this approach anytime you want to remove a file that is no longer needed in memory from memory. You can do this from any file - you do not have to be in FILECTRL.WK3. You can repeat the above process until you've documented your files. However, let's demonstrate how to create a file using FileControl first. First, place the cell pointer anywhere in the directory listing where you want to create a file. Then, press ALT-C (release 3) or CTL-C (Windows). This is the hot key to allow you to create a new file. When you do so, a menu will appear asking you to select the desired file type. Then, a row will appear and a message bar appears asking you to type in a filename (but no file type). For purposes of this exercise, give it a name like TEST or EXAMPLE. When you press enter after typing the name, the cell pointer is moved to the documentation cell for you to document the new file. Enter in something like "Test file to illustrate how to create and document a new file" and press enter. A new file will then be created and saved in the directory and by the name you requested. And, you document files as you create them. Please note that you can use 1-2-3 to edit and change the documentation anytime you need to do so. Now go back to FILECTRL.WK3 by pressing ALT-M or CTL-M. Remove the file you created from memory by pressing ALT-M or CTL-M and selecting "X-MEMORY". Highlight the new file and press enter to remove it from memory. Now let's illustrate how the RENAME option in FileControl works. Place the cell pointer anywhere on the row of the file to be renamed. Press ALT-M or CTL-M to display the first menu. Select the RENAME option. Once you do this, you are moved to another section of the spreadsheet and asked to enter a new name. Do so and press enter. The screen will blank momentarily, and the EXE file that was read in during the install process takes over and renames the file. Then control is returned to the spreadsheet, and the name on the file row is changed, and the set of files are re-sorted. You will notice that the documentation stays with the file. Also, if there is a format file (Always, Impress, or WYSIWYG), it is renamed at the same time. Please note that this is true only if your worksheet has been saved using the default 1-2-3 file types (WK3, WK1, WKS). If you have used other file types, then the format files are not copied, renamed, etc. The CTRL123.EXE file is called whenever a file MOVE, COPY, RENAME, OR DUPLICATE option is used. It is also called when you do a number of directory functions such as copying, erasing, or creating a directory. The blanking that takes place is normal as control is transferred to the EXE file. Once it completes its task, control is returned to the spreadsheet. MANAGING NON-WORKSHEET FILES ---------------------------- As mentioned earlier, FileControl CAN BE USED TO HELP YOU KEEP TRACK OF NON-WORKSHEET FILES, such as text documents or graphics files. The following illustrates how to use FileControl to do this: First you would set up a separate section for the files. The next step is to run an update on this section to get a listing of the files. On the files that you obviously recognize, go ahead and document the files. To document the remainder, first printout this section and load in the software that created the files. Use it to view the unknown documents. Make note of what they are on your printout. If you want to rename a document, mark down a new name. And if you don't need the document, make a note of that also. Next return back to FILECTRL.WK3. Now you can go to the section on your these files, and add documentation, rename and delete files per your notes. You would repeat this process for each different type of file (for example, PRN files, ZIP files, DOC files, etc). As with any system, the first time is painful. However, once you've got the bulk out of the way, future documentation and file maintenance can be done once a week or so, and in just a few minutes. SETTING UP BUTTONS AND ICONS ---------------------------- If you are using 1-2-3 release 4 for Windows or release 3 for DOS, you can add buttons to your toolbar that activate various FileControl features. You can not do this for release 1 for Windows. In release 1, although you can assign a command to an icon, when you need to move the cell pointer while running an icon activated command you can not. This has been reported to Lotus, but as of the release of this documentation, they have not solved the problem. If you are using release 4 for Windows, then you would choose "Tools *Smart Icons*Edit Icon". You can either select a customizable icon, or a standard icon. If you select a standard icon, then 1-2-3 creates a copy by a different name so you do not change the original icon. In release 3, you would click on the icon that is the letter "U" and a pencil point. You then select the icon to customize. You can edit the icon to look like what you want. Once you have assigned the macro and edited it, you can add it to your first icon bar. You do this be using the 1-2-3 icons that allow you to customize this bar. You can use either the hot key name ( \ + letter) or the macro name when you set up an icon. The preferred way is to use the hot key name, as this prevents the macro from accidently running in another spreadsheet. The names are: Hot Key Macro Name Name Description \M MAIN_MENU Returns to FILECTRL.WK3 if in another file, or displays the main menu if you are in the worksheet. \C CREATE_A_FILE Creates a file. \O OPEN_A_FILE Opens a file \G GO_TO_A_FILE Displays a list of files that are in memory so you can go directly to the one you wish to be in. \X X_MEMORY Displays a list of the files that in are memory so that you can remove a file from memory. \F FIND Displays the search menu \N NEXT_SECTION Takes you to the next section of files. \P PREV_SECTION Takes you to the previous list of files. Both the macro name must be enclosed in {}'s when you type them in. For example to assign the open a file macro to an icon you would type: {\0} or {OPEN_A_FILE} If you assign the macro "MAIN_MENU" to an icon, you should include the full path and the filename. If you store FileControl in a directory called C:\WKSFILES, then you would enter the following: {<>MAIN_MENU} Please note the spelling of the worksheet name: it is FILECTRL, not FileControl. Also note that you would replace "C:\WKSFILES" with the path where you have stored your copy of FILECTRL.WK3. Including the path allows this macro to work when you are in another spreadsheet. This macro returns you to the FileControl spreadsheet when you are in another sheet. And if run when you are in the FileControl spreadsheet, it displays a menu. If you assign a macro to an icon and get an error message when you click on the icon, then check the spelling. FILECTRL.WK3 LAYOUT ------------------- FILECTRL.WK3 consists of several sheets. The file information is kept on sheet A. This sheet is sometimes referred to as the "FILE MANAGER" part of FileControl. Lists of files that you want to open, save, or close all at once are kept on sheet B. This is referred to as the "WORK GROUP MANAGER" part of FileControl. The macros are stored in sheet C. The macro sheet is kept hidden. Another feature of FILECTRL.WK3 is that the columns beyond column G are all hidden. This is done so that you don't accidentally move the file names off the screen. And, to keep you from thinking you can use all that open space to the right of the file documentation. To provide organization in FILECTRL.WK3, groups of related files are organized into sections. You can have as many sections as you want. The following are examples of a section: - files that are on a floppy, - files in a directory, - the files in a directory plus all the files in that directory's sub-directories. Each section has the following identifying information, as illustrated by the following example: +---------------------------------------------------------------------------+ |DIRECTORY: C:\WKSFILES | |FILETYPE: WK* 3318 1 <- Do Sub-directories (1=Yes, 0=No) | |LAST UPDATED: 11/06/93 09:58 1 <- Display empty directories (1=Y,0=N) | |DESCRIPTION: Lotus 1-2-3 worksheets | +---------------------------------------------------------------------------+ The directory entry tells FileControl which directory to scan for files. Examples of some other possible directories are: - C:\WKSFILES - R:\SHARED\ANALYSIS - A: - B:\MISC As these examples illustrate, a floppy drive can be listed as a directory. This tells FileControl to scan a floppy disk for files. You could, if you want to, use C:\ as the designated directory. However, you are much better off using sub-directories of your main directory, as files of a given type are typically grouped in sub-directories of the root or main directory. Also, it is a lot faster to do an UPDATE on just the directories needed vs. all the directories on your disk. For example, C:\WKSFILES is a sub-directory of C:\. Your worksheets could then be stored in this directory and in its sub-directories. For example, cost studies could be stored in a directory called C:\WKSFILES\COST. The spreadsheet file allows you to easily create such directories and to copy or move your files into them. Your entry for a directory in a section heading must meet certain simple format requirements. For example, it must contain the full path of the directory starting from the first directory. Thus, it must begin like C:\directoryname\directoryname... Examples of incorrect entries are 123R3 (does not contain path information) and \WKSFILES (path information incomplete). Also, you do not need to end the directory name with a "\". If you do, FileControl will remove it as it is not needed. When FileControl scans the designated directory, it looks for files of the specified file type. In the above example, the type is WK*, with the "*" representing a wild card character. This particular file type entry scans for WK4, WK3, WKS and WK1 files. Other examples of valid file types are PRN, DOC, ZIP, TXT, BAT, EXE, and * (for all files). Thus, FileControl can help you maintain documentation on all your files. One caution: Do not use a "*" for the file type unless you really want to see all the files in a directory. When you specify the directory, you also have the option of scanning or not scanning the directories under the specified directory. A value of "1" for "Do sub-directories" scans for sub-directories. A value of "0" does not. You also have the option to display or not display the directories that do not contain files that match the specified file type. This is controlled by the value set for the "Display empty directories" option. You can use WYSIWYG to color the cell containing the directory name and the three cells to the right. If you do, all the directories of the section are given this color coding after an UPDATE is run. This helps you readily identify which section you are in, as well as identifying each directory. You can also color code individual files. However, that coding will be retained only if there is an entry in the file's documentation cell. (If it is important enough to color, it is important enough to document.) Do not use WYSIWYG to draw lines or boxes around files. Lines and boxes are not attributes of cell contents, so are not retained when an Update is run. The line in the section heading with the title "DESCRIPTION:" can be used to provide a description of the section. In the case when A: or B: are specified, you can use the description line to record the floppy label information. Use of the description line is optional. To scan for files once you've filled in the directory and type, type ALT-M (release 3) or CTL-M (Windows) to bring up the first FileControl menu. Go to the second menu and select UPDATE. You are then asked to confirm your request. When the update is complete, you will see a list of files by directory. The directory name is at the top of each group of files. Each directory and its group of files are separated from the others by a blank line. Do not remove these blank lines. They are needed to allow the macros to find the beginning and ending of a a directory's list. CREATING NEW SECTIONS AND DIRECTORIES ------------------------------------- Creating new sections and directories is very easy. To create a new directory, just select the option on menu 2 labeled "CREATE_A_DIRECTORY". When you select this option, it asks you to put the cell pointer either on a blank row or in a section heading. The first option (the blank row), is the one you will use most often. It allows you to create sub-directories of the directory listed in the section heading. After you have put the cell pointer on a blank row and pressed enter, two new rows are added, and the directory from the section heading is copied to this row to establish the path for the new directory. To this you add the name of the new directory. Once you have done this, you are asked to confirm your request, and then FileControl creates the new directory for you. If you decide not to create a new directory, put the cell pointer on a non-blank row or just don't enter a new directory name when prompted to do so. Please note that you do not have to create new directories in alphabetical order. When you do an update, the directories will be sorted in aphabetical order for you. If you wish to create a new section, select the option on the 2nd menu labeled "NEW_SECTION". You are then askthey will be sortd eed to place alphabetically for you.the cell pointer on a blank row just below where you want the new section. Finally, you are asked to confirm your request before the section is added. If the new section is for a new directory, first create the new section. Then, in the cell with the label "Enter path and directory here", enter the path and directory for the new directory. Then choose the "CREATE_A_DIRECTORY" option on menu 2. Instead of putting the cell pointer on a blank row, put it on the path and directory you just entered. This will create a new directory for the section. If you decide that you don't want a section you have created, you can remove it without affecting the files. For example, say that you created a new section to document the files on a floppy and now you don't need it. Just use the 1-2-3 commands to delete the un-needed rows. This removes it from the spreadsheet, but doesn't delete the files. However, if you wish to delete the files and directories, then you will need to use the "ERASE" option on menu 1. Once you have deleted the files and directories, use the 1-2-3 commands to remove the rows containing the section heading. UPDATE OPTIONS -------------- You have the option of updating a single directory that is listed in a section instead of updating all the directories in an entire section. To update a single directory instead of all the directories for the directory listed in the section heading, just put the cell pointer in the directory you want to update and select the update option from the menu. Do this when you want a quick update of a single directory. You do not need to run the UPDATE option every time you retrieve FILECTRL.WK3. Run an UPDATE if you have issued DOS commands outside of the Manager that affect the files in a given section. Or when you want to update the date, time and size information on your files. UPDATE SPEED ------------ The amount of time it takes to do an UPDATE depends on the number of files and sub-directories, the number of files documented, and the speed of your computer. For example in release 3 for DOS, a 386 16 Hz machine will scan and update a section containing 100 files in 15 directories in about a minute. A 486 66Hz machine will do this same chore in 5-10 seconds! If you are running 1-2-3 for Windows, an update takes about 2x the time of the release 3 time. It also depends on how many files are loaded into memory. If you have a lot of files in memory, then you should turn re-calculation off (/ Worksheet Global Recalc Manual). This can dramatically speed up the commands. Also, if you have very little memory left (check by "/ Worksheet Status"), then everything slows down. THE HOT KEYS AND WHAT THEY DO ----------------------------- Eight hot keys have been set up to make it easy to do certain functions. If you are using Release 3, then the hot keys are activated by holding down the ALT key and pressing the appropriate letter. If you are using Windows, you would hold down the CTL key instead. In the remainder of the manual, this is referred to as ALT/CTL. The hot keys are: Hot key Function M Returns to FILECTRL.WK3 if in another file, or displays a menu if pressed while in FILECTRL.WK3 N Goes to the next section of files P Goes to the previous section of files F Finds text, to help locate a particular file O Opens (retrieve) a file Also works on a file listed in a work group. Work groups are discussed later. C Creates a file G Goes to another file in memory X Removes a file from memory Only the M, X, and G hot keys will work if you are not in FILECTRL.WK3. This is to prevent the other hot keys (O, C, N, P, and F hot keys) from accidently being run in the wrong spreadsheet. IF YOUR COMPUTER BEEPS when use the M, X, and G hot keys when you are in another spreadsheet, then that means you have a macro by the same name in another sheet in memory. Since 1-2-3 detects two macros by the same name, it doesn't know which one to run, so it beeps instead. If you have already set up macros in Lotus that use the above hot keys, you can remove all but the ALT/CTL-M hot key so that you can use the macros you've assigned to these keys. The macros for creating a file, opening a file, searching the spreadsheet, or removing a file from memory are available from the menus that appear by pressing ALT/CTL-M. If you remove the N, P, or G hot keys, you lose these functions. You can also remove the ALT/CTL-M hot key if you set up an icon to bring up FileControl's main menu (except in Release 1 for Windows). This is also true for the other keys. To remove a hot key, you need to delete the range name "\" + letter. For example to remove the ALT/CTL-C hot key, you would issue the commands \*Range*Name*Delete*\C. If you realize later that you want the hot key back, you can add it back by issuing the commands \*Range*Name*Create, type a "\" plus a letter, press enter, and then type in the appropriate macro name which is found in the earlier documentation on assigning these commands to icons. This will assign the "\" + a letter to this macro name. The ALT/CTL-M hot key has several functions. If you press it when you are in a different worksheet other than FILECTRL.WK3, you are returned to FILECTRL.WK3. If you press it while on sheet A of FILECTRL.WK3, it brings up the first of FileControl's menus used to manage files. If you press it while on sheet B, it brings up the menus used for work groups (these are discussed in detail later). The next two hot keys, N & P, allow you to move directly from one section to another. For example, if you were in a section consisting of a hundred files, and wanted to go to the next section, you could page down repeatedly. Using ALT/CTL-N would take you to the next section in one key stroke. The ALT/CTL-F hot key allows you to find text in FILECTRL.WK3. Text can be the name of a file, the name of directory, or any of the text in the documentation you have typed in. Multiple words are allowed. When you press this key, you are given several search options: - Search just the current section - Search all columns, all sections - Search just the file name column - Search just the documentation column - Continue the search downward from the current location. If the text you are searching for is not found on the first try, you are automatically given the option to change your search request. Once a match is found, you can repeat the search until all occurrences are found. An error message will appear when no more matches are found. This is normal. Press Quit to stop searching for text. Use the ALT/CTL-O hot key to open any 1-2-3 spreadsheet listed in FILECTRL.WK3. Opening a file is the same as retrieving it, except that the current file is not removed from memory. Thus, this key retrieves the file and keeps FILECTRL.WK3 in memory so that you can go back to it whenever you want. This hot key has some helpful options: - You can press it to retrieve a file and be anywhere on the row containing the file name. You do not have to be on the cell containing the file name. - If you've just done a search and found the file you want to retrieve, don't worry about pressing "Q" to quit the search menu. Just press ALT/CTL-O. It will back you out of the 1-2- 3's menu automatically and open the file. - You can also use the ALT/CTL-O key to open a file from that is listed in a work group (which are discussed later). There is also an option to open a file that is found on the first menu. This option is useful because it gives you the choice of opening a file that is found on a floppy or in a directory not listed in FILECTRL.WK3. The ALT/CTL-C hot key is the best way to create a new file. Before pressing it, put the cell pointer in the directory where you want the new file. You can be anywhere in the directory listing. After you press it, you are asked to select a file type. After you specify the file type, FileControl opens a row and asks you to enter a file name. (If you decide at this point not to create a file, just press enter. FileControl will then delete the new row and say no file created.) After you enter the documentation, FileControl will re-sort the file list and then create the file. Both the new file and FILECTRL.WK# are in memory. (You can have many files in memory at one time.) It then does a quick save to the specified directory so the file is saved to the right location. The ALT/CTL-G hot key allows you to go directly to another file in memory. When pressed, it executes the GOTO command, and then displays a list of files in memory. To go to a file, just place the cell pointer on its name and press enter. This is a very useful hot key, as it returns you to the last spot you were in when you left the file. If you went between files using the CTL-pageup or CTL-pagedown keys, then when you return to a file you left, you have to page all the way to the sheet you were on vs. going to it directly via this hot key. You can use ALT/CTL-G from within any file. The ALT/CTL-X hot key allows you to remove a file from memory. The file is not erased from the disk. It is important to remove files from memory once you are done with them. For example, it frees up memory to load other programs. And, frequently you will want to load in a spreadsheet, check out some data, and then remove the spreadsheet without saving. ALT/CTL-X is the easy way to remove a file from memory without accidently selecting the wrong 1-2-3 menu options and deleting the file from your disk. THE MENUS --------- When you press ALT/CTL-M on sheet A, you are presented with the first of two menus used to control your files. The following are the menus and the features that are found on each. If you select an option and then decide that you do not want to execute it, just doing the opposite of what it asks will stop execution. For example, putting the file on a blank line when directed to put it on a file row. MENU 1 options OPEN When this option is selected another menu appears. This menu allows you to create a new file or to open (retrieve) an existing file. The create option is the same as using the Alt/Ctl-C hot key. The first choice on the menu that appears allows allows you to open the file that is highlighted by the cell pointer (you must pre-position the cell pointer before doing opening). Other options on this menu allow you to select the disk that the file is on, and then select the file as you would normally do in 1-2-3. This option is useful because it gives you the choice of opening a file that is found on a floppy or in a directory not listed in FILECTRL.WK3. TO COPY A SINGLE FILE, THE CELL POINTER MUST BE POSITIONED ON IT BEFORE PRESSING ALT/CTL-M. The same goes for copying a directory listed in FILECTRL.WK3. X-MEMORY This allows you to remove a file from memory that has been retrieved into 1-2-3. The file is not erased from the disk. When selected, you are presented with a list of the active files. Just place the cursor on the desired file and press enter. If you decide not to remove a file from memory, just hit a series of escapes. Please note that there is no warning that stops you from removing modified files from memory - they are removed immediately after being selected, so be careful. Sometimes when you have opened too many files in memory, you can not remove any files. The only recourse then is to exit 1-2-3. A hot key, ALT/CTL-X has been set up to allow you to use this option without having to return to FILECTRL.WK3 and calling up a menu. COPY This option allows you to copy a file from one directory to another or to a floppy drive. In addition to copying the worksheet, the format file is also copied. The row of information on the file is also copied to the destination directory. You have the options of copying the file to a directory listed in FILECTRL.WK3, to a floppy drive (A: or B:), or typing in a directory name and its path. This option also allows you to copy all the files in a directory listed in the spreadsheet to either a floppy (A: or B:), to another directory listed in FILECTRL.WK3., or you can specify the destination drive or directory. Another option it gives you is the option to copy the files from a floppy to a directory listed in the spreadsheet. Use this feature to copy your files from floppy disks to your hard drive. When you are copying files from a floppy, a list of the files on the floppy are displayed. From this list, you can select individual files to be copied. You can also use the LIST command that is on the COPY menu to first check out what files are on the floppy. If a file by the same name exists in the destination directory or floppy, you are given the option to over- write the file or leave it intact. If you want to move all the files in one directory to another, first copy the files to the destination directory. Then, use the ERASE option to erase the old files and directory. If you copy multiple files using the directory copy option, an UPDATE is run automatically on the destination directory after the copying is done. This avoids you having to do this yourself. MOVE This allows you to move a file from one directory to another. The documentation and formatting file are moved also. You can move files between hard drives using this option. BEFORE SELECTING, BE SURE THAT THE FILE YOU WANT TO MOVE IS ALREADY HIGHLIGHTED BY THE CELL POINTER. RENAME This option allows you to rename a file to a different name. The file type can not be changed. BEFORE SELECTING, BE SURE THAT THE FILE YOU WANT TO RENAME IS ALREADY HIGHLIGHTED BY THE CELL POINTER. DUPLICATE This allows you to make a copy of a file and give it a different name, so that both the original and duplicate file are in the same directory. This option is very useful if you routinely create new spreadsheets by modifying an existing spreadsheet. BEFORE SELECTING, BE SURE THAT THE FILE YOU WANT TO DUPLICATE IS ALREADY HIGHLIGHTED BY THE CELL POINTER. Say for example that you create a new report each month. What you can do is to use FileControl to rename the current spreadsheet you are using to the month's name (Jan, Feb,...). Then when you go to do next month's report, you duplicate the old spreadsheet and give it the name of the next month. And you can go one step further. Create a new directory whose purpose is to store just these spreadsheets. Then use the MOVE option on menu 1 to put these spreadsheets in this directory. This gives you a much better organization to your files. ERASE This option allows you to erase a file from your disk. Files that are erased from the disk are gone. So, be careful. If you accidentally erase a file, and then realized that you should not have, do not save or copy any files to your disk. And, get help from someone who knows how to recover a deleted file. However, recovery may not be possible. BEFORE SELECTING, BE SURE THAT THE FILE OR DIRECTORY YOU WANT TO ERASE IS ALREADY HIGHLIGHTED BY THE CELL POINTER. If eraseing a directory, you can be anywhere in the directory listing (including its files) to select the directory). This option also allows you to erase all the files in a directory or on a floppy. Before you erase the files, you can use the LIST option that appears to see a list of the files. Also, before the files are erased, you are presented with a list of the files to help you confirm that you truly want to erase them. If you want to do a selective erase of a floppy, or if the floppy contains directories, then you should create a new section for the floppy and scan for files and directories using the UPDATE option. Then use the file erase and directory erase options to remove the un-needed files and directories. 2nd MENU This option takes you to the second of FileControl's file management menus. MENU 2 options: UPDATE_INFO This option allows you to update the file list an entire section and all its directories or just a single directory New files are automatically added and the rows for files which no longer exist are automatically removed.. Documentation and WYSIWYG color or other formatting on a file will be retained through an update (if the file has been documented). BEFORE SELECTING, BE SURE THAT THE SECTION OR DIRECTORY YOU WANT TO UPDATE IS ALREADY HIGHLIGHTED BY THE CELL POINTER. You can be anywhere in the section or directory (including the file list) when you select this option. CREATE_A_DIRECTORY This allows you to create a new directory. By using multiple directories, you can better organize your files. Groups of related files should be kept in their own directory to make it easy to find and manage them. To create a new directory for a new section, first create a new section. Then, in the cell to the right of the word "DIRECTORY:", type the full path and name of the needed directory. Then, run this menu option. To create a directory within an existing section, put the cell pointer on a blank row in the section and press enter. FileControl opens up a new row and asks for the name of the directory. You will notice that FileControl puts the directory heading information from the section information in for you. This is required, as the new directory must be a sub-directory of the directory listed in the section heading. NEW_SECTION This option allows you to add a new section. For example, say that the first section is for files in a directory called C:\WKSFILES and its sub-directories. If you have worksheets stored in another directory (say C:\INACTIVE), then you would want to add a new section to keep track of those files. If you like to keep your files on floppy disks, you can create a new section for each floppy disk. Each section is independent of the other sections. In this case, the directory name would be A: or B:, depending on which drive you use. Then, you would use the description line in the section heading to describe which floppy this section is for. Be careful where you add a new section. It should be after all the files in the previous section. Use the first section in FILECTRL.WK3 for the files you use most often. Its easiest to get to (by pressing the home key). And, each time you open FILECTRL.WK3, the cell pointer is placed at the top of sheet A. If you create a new section and then realize you don't need it, just use the 1-2-3 commands to delete the rows containing the new section. FIND This option is the same as pressing Alt/Ctl-S. It allows you to search the spreadsheet to find a file or a directory. Options include searching just the section where the cell pointer is located, just the file names, just the documentation, or all of the spreadsheet. SORT This option allows you to sort either a single directory or all the directories in a section. You can sort by file name, date and time, or file size. If you are in a directory when it is selected, then only the directory is sorted. If you are in a section when this selected, then all directories of the section are sorted. Sort options are file name, date & time, and file size. Please note that when you run an update, the files are re-sorted in alphabetical order. ABOUT This option displays information about the release of FileControl. 1st MENU This option takes you back to the first menu. COPY, MOVE, AND RENAME WARNINGS ------------------------------- If you move, copy or rename a file that is referenced in formulas by other files, then those files will continue to refer to the old name and old location of the file. If the file has been moved or renamed, cell references to the old name or location will return an "ERR" instead of a value. To fix this, you must manually edit the files to refer to the new location or name of the file. Using the search and replace feature makes this a fairly simple task. Also, the format files are copied, moved, or renamed only if your worksheet has been saved using the default 1-2-3 file types (WK3, WK1, WKS). If you have used other file types, then the format files are not copied, renamed, etc. HOW TO OPEN A FILE ON A FLOPPY ------------------------------ Let's say someone walks into your office and has a file that you want to load into 1-2-3. DON'T ISSUE THE COMMANDS "/ File Retrieve". This will remove FILECTRL.WK3 from memory. Instead, bring up the main menu of FileControl and select the "OPEN" option. Then select the drive that the disk is in. From the list that appears, highlight the file and press enter. The file will then be opened. Once you are done, save the file and then remove it from memory. If you remove the disk before removing the file, 1-2-3 gets upset with you. HOW TO COPY A FILE FROM A FLOPPY DISK TO A DIRECTORY ON YOUR HARD DISK ---------------------------------------------- Let's say someone walks into your office and has a file that you want that is on a floppy disk. How do you get it stored on your hard drive? First, decide if you want to put the file into an existing directory or into a new directory. If an existing one, then you can go to the next step. If a new directory is needed, first create a new directory using the CREATE_A_DIRECTORY option on menu 2. Next, Press ALT/CTL-M and select the COPY option. Choose the floppy drive containing the disk. You are then asked to specify the destination directory. Control is then transferred to the FileControl support program. A list of files on the floppy are presented, and you can then select the files to be copied. After the copying is done, an UPDATE is run automatically. Once the update is complete, you will see the new file and can add documentation. FINDING ALL YOUR FILES ---------------------- If you are uncertain where all your files of a given type are, then do the following to find them. - Add a section (using menu 2) at the end of one of your existing sections. - In the directory cell, enter "C" for the C-drive, (or "D" for the D-drive, ...) - Make the file type the type desired. "WK*" would result in all 1-2-3 spreadsheets being found. Although you can use "*" as the file type, this is not recommended. You probably have one to two thousand files on your disk and don't realize it. Although a "*" will work, it will take a few minutes to do the update, and you will have too many files to document. - Set the options on doing sub-directories to a "1" - Set the option to show empty sub-directories a "0" (this will keep empty directories from being displayed). Finally, run the UPDATE option on this section. Once you have identified where all your files are, use the other options in FileControl to organize the files into the directories that you want them stored in. If necessary, add new sections and directories to organize your files. PRINTING -------- FileControl does not come with any menu options that print your file information. You can use 1-2-3 commands to print. To maximize the amount of documentation that is printed, print in compressed landscape mode. THE MACROS ---------- The macro code that makes FileControl work has been placed on a hidden sheet in the file. DO NOT UNHIDE this sheet. And, DO NOT CHANGE THE MACROS. The macros are not simple. If you do go and look at the macros, in some cases you will say "I can write this command better". DON'T. Some things have been done in certain ways to avoid problems that can occur in just the right situations. Also, there are a lot of error checking routines that you should leave alone. You will also see a number of range names in this worksheet. DO NOT DELETE ANY OF THEM. They are there for a purpose. MORE WORKSHEET DON'TS --------------------- In addition to the first commandment above (DON'T CHANGE THE MACROS), there are several other commandments you must obey: DON'T ADD OR DELETE ANY COLUMNS - ANYWHERE DON'T CHANGE THE LAYOUT OF THE SECTION HEADING. DON'T PUT ANYTHING IN COLUMN A. DON'T ISSUE THE / FILE RETRIEVE COMMAND from within FILECTRL.WK3. It will result in the retrieved file replacing FILECTRL.WK3. If you do this, you will need to re-open the spreadsheet. DON'T SAVE FILECTRL.WK3 AS A WK4 FILE. The macros will not work. DON'T CHANGE THE TOP FOUR ROWS LISTING THE HOT KEYS. DON'T CREATE A NEW SECTION BY USING 1-2-3 COMMANDS TO COPY AN EXISTING SECTION. DON'T MOVE A SECTION (unless you include all of column A - see the comments in the what you can do section). DON'T REMOVE THE BLANK ROWS THAT SEPARATE DIRECTORY LISTINGS, and don't delete row 4 which is blank. If you disobey the above commandments, things will not work right. It's that simple. For those of you who don't believe this, perhaps the following will help convince you: - The macros use column A and the cells in this column with a space in them to find the beginning and ending of a section. and its files. If you put something else in column A, or remove the cells with a space by them, then the macros won't work correctly. - The macros look for information to be in certain columns and cells, so if you delete or add columns, then the macros won't find the information, and they won't work correctly. This includes spaces put in column A to identify the start of a section. - The same goes for the layout of the section heading. The macros look for information to be in certain places. When it isn't were it's supposed to be, the macros won't work right. THINGS YOU CAN DO ----------------- Delete entire sections and all the rows in them. This is the easiest way to get rid of a section you don't want listed in FILECTRL.WK3. Please note that the files still exist and are not erased. Change column widths and formats. Colorize and do any WYSIWYG formatting to any file name or its documentation. Please note that the WYSIWYG formatting will only remain on files with documentation when an update is done. (Boxes and lines will not be retained if drawn around a key file) Change the column widths to whatever size you want. Move a section and its file and documentation list to a new location - IF you include column A in the move command. If you don't include column A, then sooner or later when you do an update, your information on the section without the blank will disappear. THE WORK GROUP MANAGER ---------------------- Sheet B of FILECTRL.WK3 is referred to as the "WORK GROUP MANAGER." This feature of FileControl allows you to open, save, and close groups of worksheets. This gives Lotus 1-2-3 users functionality very similar to what Microsoft Excel users have with their work group feature. Thus, instead of individually having to load or open a group of files, you can automate the task. Any group of files that you want to have in memory at the same time are referred to as a work group. The following illustrates what sheet B would look like if set up to open two work groups: +------------------------------------------------------------+ | CTL-M - DISPLAYS WORK GROUP MENU | | CURRENT GROUP: COST ESTIMATE WORK GROUP | | | | COST ESTIMATE WORK GROUP | | C:\WKSFILES\ESTIMATE | | COST.WK4 manufacturing cost | | SALES.WK4 mktg cost and sales | | | | MONTHLY REPORT WORK GROUP | | C:\WKSFILES\MONTHLY | | DATABASE.WK3 data for all reports | | PREMISES.WK3 premises for expansion | | SPECDATA.WK1 data for these reports | | JANUARY.WK4 | +------------------------------------------------------------+ Notice that each group has a title or description on its first row. Then it is followed by the directory where the files are found, and then by the list of files. Each group is separated from each other by blank lines. Documentation has been entered to the left of the work groups. This documentation can be descriptions of the files, or comments as to which files reference which files. The titles of each work group in this case has been bolded and underlined to make it easy to find. You could also use WYSIWYG formatting to colorize the work group title, and any of the files. The group listed after the words "CURRENT GROUP" is the one that FileControl is set up to open, save, or close. The current group is change through the work group menu. When you first go to sheet B, it will not have any work groups, and will look like the following: +------------------------------------------------+ | ALT-M - DISPLAYS WORK GROUP MENU | | CURRENT GROUP: NONE SPECIFIED | | | | | | ENTER WORK GROUP TITLE IN THIS CELL | | Enter file directory and path in this cell | | xx replace xx's with filename.type | | xx | | xx | | | | ENTER WORK GROUP TITLE IN THIS CELL | | Enter file directory and path in this cell | | xx replace xx's with filename.type | | xx | | xx | +------------------------------------------------+ As this illustration shows, several cells have already been set up for your work groups. You are not limited to just this number of work groups - you can add additional rows for more work groups. When you setup your work group lists, keep the lists in column B. This makes it easy to page up and down to find a particular work group, and the macros only work for groups setup in column B. To use the cells above, replace the title line with your work group title, the directory line with a directory and a path, and the XX's with your file names. You can add additional lines if they are needed for your work group file list. You must have at least one blank line between work groups. The following discussion explains in detail how to create a work group listing. WORK GROUP INFORMATION NEEDED ----------------------------- A block of information is needed on each work group. The top row must be a comment statement that describes the work group. This statement should be 30 characters or less. Typical comment statements are "FORECAST WORK GROUP", "SPECIAL EXPANSION STUDY FILES", and "MONTHLY COST ESTIMATE WORK GROUP." You can also enter comment statements to the right of the file names and use WYSIWYG options to colorize the comment row and the file names. You may add as many rows as you need to add new work groups. Be sure to separate different work groups by a blank cell or row. Use only sheet "B" for work groups, and use only column B. On the second row of a work group, enter the full path and directory where most or all of the files are stored. Examples of typical paths are: C:\WKSFILES C:\STUDIES\EXPAND A: This directory is called the primary directory. Please note that all the files DO NOT have to be in the primary directory - it is typically the directory where most of the files are found. As the examples above illustrate, you can use a floppy drive as a primary directory. If you do, be sure to include the colon after the drive letter. In each subsequent row, enter in a file name AND FILE TYPE. Do not skip any rows. Again, BE SURE TO ENTER THE FILE TYPE. In most cases, the macros will work without the file type, however, there are situations where it is needed. As many files as you wish (and your computer's memory allows) can be in a work group. The only caution is that if you enter a file in two work groups and try to load both groups, the request to load the second work group will crash when the macro tries to open the file that is already in memory. And, subsequent files will not be opened. ALSO, WKS FILES ARE NOT ALLOWED BECAUSE 1-2-3 CONVERTS THEM TO WK3 FILES AND THIS CONVERSION IS DETECTED AS AN ERROR AND STOPS THE MACRO. If a file is in the primary directory (the directory listed at the top of the work group), then only the filename need be entered. If the file is in a sub-directory of the primary directory, then you need only enter the directory and the file name. DO NOT put a "\" in front of the directory name. If the file is in a completely different directory, then enter the full path and filename. The following illustrates a typical work group and the above options: EXPANSION STUDY WORK GROUP C:\WKSFILES\EXPAND Directory for most of these files US.WK3 EUROPE.WK3 ASIA.WK1 SALES\LOWVOL.WK4 these files are in different directories SALES\HIGHVOL.WK3 SPECIAL\COST.WK3 supporting cost info C:\WKSFILES\ITEMS.WK4 list of items studied C:\ECONS\PRODLIST.WK4 In this example, the worksheets US, EUROPE, and ASIA are all located in directory C:\WKSFILES\EXPAND. The files LOWVOL and HIGHVOL are located in a directory called C:\WKSFILES\EXPAND\SALES. Since the directory SALES is a sub-directory of EXPAND, only its name need be entered. File COST is located in another sub-directory of the EXPAND directory. Files ITEMS and PRODLIST are located in directories that are not sub-directories of directory EXPAND. Thus the full path showing where these two files are found is entered. If you are uncertain on whether or not to enter the full path, enter it. Doing so causes no harm. The text "supporting cost info" and "list of items studied" in the above example illustrates how individual files in a work group can be documented. One type of documentation you should consider putting to the left of the filenames are which files are referenced by each other. The text at the top of the work group example, "Expansion Study Work Group", illustrates the name that is required at the top of a work group. Only a single line of documentation can be entered at the top of a work group. However, you can enter all the documentation you want to in the cells to the right. Do not enter blank lines between files to add more documentation - a blank line indicates the end of the files to be loaded. If you want, you can load more than one work group. The files in the second work group you load are opened after the last file in the first work group. Make certain that you don't open the same file twice. If you do, the macro will stop when 1-2-3 tries to open the duplicate file, and subsequent files will not be opened. Please note that file management activity on sheet A does not affect the work group lists. For Example, if you rename a file on sheet A, you will need to manually change sheet B. Or, if you erase a file listed on sheet A, you must manually remove it from any work groups that it is in. THE WORK GROUP MANAGER MENU --------------------------- To run the Work Group Manager functions, go to sheet "B", and press ALT-M (Release 3) or CTL-M (Windows). The menu that appears has the options to change, open, save, and close work groups. Closing means to remove files from memory. The files are not erased from the disk. - The first menu option (CHANGE) changes or updates the work group listed in the other selections. Once it is done, it returns you to back to the menu so you can then open, save or close the work group. If the last time you used this option you constructed the macro that is now needed, you do not need to re-construct the macro unless you have changed the work group file list. Instead, just choose the first option. But if you have changed the file list, you must run the CHANGE option in order to update the Open, Close, and Save selections. Otherwise the old list of files will be used by these options. - The other three selections allows you to open, save, or close the specified work group. Please note that if you close a work group (remove its files from memory), modified files will not be saved, nor are you warned that this is happening. - If you do a SAVE ALL MODIFIED FILES after you run the SAVE option, you will notice that a file is sometimes saved. This is another file that has been changed that is in memory. For example, it might be FILECTRL.WK3. If you want to confirm that your files were saved, run an update on Sheet A of FILECTRL.WK3 and look at the date and time saved on the files in the work group. WORK GROUP MANAGER SPEED AND MEMORY ----------------------------------- How fast the various Work Group File Manager commands take to execute depends not only on the speed of your computer, but also on how many files are loaded into memory. If no other files are loaded into memory, they run extremely fast. But, if you have a lot of files in memory, then you should turn re-calculation off (/ Worksheet Global Recalc Manual). This can dramatically speed up the commands (it will also speed up all the other work you do). Also, if you have very little memory left (check by "/ Worksheet Status"), then everything slows down. The biggest problem you will run into is running out of memory! (Because it is easy to load multiple files.) Each file you open in takes a hunk of your memory, so keep an eye on your available memory by issuing the commands "/*Worksheet*Status". If you get the error message "MEMORY FULL" when opening or closing a work group, then you have run out of memory. At this point it is impossible to close a file. The easiest solution is to exit 1-2-3 and return. Long term, you should add more memory to your computer if you frequently run into this problem. WORK GROUP OPEN, SAVE, OR CLOSE ERRORS -------------------------------------- When you run one of the work group options, it is possible that an error will occur. If one does occur, note which file you end up on after you clear the error message. The file reference which is causing the error is the one listed after this file. The following are the various situations that can cause this to happen. - The file name is spelled wrong - You have the wrong file type listed for the file - The directory and path statement is incorrect or spelled wrong - If the error occurs while opening the file, either - the file doesn't exist, - the directory where it is stored can not be accessed, - the wrong floppy is in the drive, or - the file is already loaded in memory. - If the error occurs while saving or closing, then the file is not in memory - You are out of memory. This will affect both loading and closing files 1-2-3 SPREADSHEET REFERENCES ---------------------------- When working with 1-2-3, you should always open all files that have formulas that reference each other. Otherwise, if you add rows or columns to one such file, and the other files are not in memory, their references are not updated and they may no longer be correct (unless only range names are used in the references). Thus the advantage of the Work Group Manager: It allows you to easily open, save, and close groups of files that need to be worked on together. 1-2-3 SAVE PROBLEM, AND THE SOLUTION ------------------------------------ For users of 1-2-3 release 3.0 through 3.4 and 1-2-3 release 1 for Windows, please be aware that the 1-2-3 menu command to save all modified files DOESN'T ALWAYS SAVE ALL MODIFIED FILES. The following describes the problem: Say you have two files, "A" and "B" which you have just saved. Then you modify file "A" by adding rows or columns. Then you issue the save all modified files command and exit 1-2-3. If file "B" has formulas that reference to file "A", these formulas will not be updated on the saved version of file "B" - even though the formulas you see in file "B"'s cells have changed if you examine them before exiting. The Work Group Manager save command SOLVES THIS PROBLEM. When the Work Group Manager's SAVE command is issued, it creates a macro that goes to each file and issues the commands "/ File Save", then specifies the file and issues the replace command to save the file. It then goes on to the next file and repeats this action until all files in the work group have been saved. AUTOMATICALLY OPENING FILECTRL.WK3 ---------------------------------- If you did not install the AUTO123.WK3 worksheet via the install program, you can create an AUTO123 worksheet with a macro to automatically load FILECTRL.WK3 whenever you load 1-2-3. By it being loaded each time you are in 1-2-3, you will tend to use it. The AUTO123 worksheet should be in your default directory and have a macro named "\0" (that's a zero, not the letter O) in it. This macro should have the following line in order to load FILECTRL.WK3: /FR{CE}FILECTRL~ When you load FILECTRL.WK3, it executes a macro to setup the spreadsheet for your use. Most of what it is doing is storing the name of the file, hiding the macro worksheet, determining if WYSIWYG is loaded, and setting the titles. Also, it changes the header rows depending on whether you are in 1-2-3 for Windows or in Release 3. If for some reason you change the name of the spreadsheet or save it to a different directory while you are using the file, you will need to run this macro. Do this by pressing ALT/CTL F3, and type "SETUP" as the macro to be run. If you don't, some of the macros will not work until either you do so or until you retrieve the file again. UPGRADING TO A NEW RELEASE -------------------------- To upgrade your copy of FileControl to a new release, run the install program. It will rename your existing copy of FILECTRL.WK3 to OLDCTL.WK3. After this is done, go into 1-2-3. When you do, the new version of FILECTRL.WK3 will load automatically, assuming you have setup your AUTO123.WK3 file to do that. If not, load it manually. Then do the following: - Retrieve the old file manually by issuing the commands "/*File *Open*After". and selecting "OLDCTL.WK3". This is the one with all your file documentation. The installation program renamed it to this name. - In the new version press ALT/CTL-F3. This brings up a 1-2-3 menu that allows you to run a macro. Type the name "COPYINFO" and follow its instructions. TYPICAL PROBLEMS AND SOLUTIONS ------------------------------ Problem: I ran the install program and everything worked fine and CTRL123.EXE would run. However, now when I run the commands like the UPDATE command, I get a response like CTRL123.EXE not found. Solution: It sounds like either CTRL123.EXE was erase, moved, or the path that is active when you run 1-2-3 has change. If the path was changed or the file was moved, move CTRL123.EXE to a directory in the path that exists when 1-2-3 runs. If erased, copy the version of CTRL123 that you got originally to the above path. Problem: The installation ran find, but CTRL123.EXE doesn't run when I do the ABOUT command , UPDATE command, etc. Solution: The directory where you installed CTRL123.EXE is not in the path that is active when you are running 1-2-3. If you used the default, this means that CTRL123.EXE was installed to your root directory (C:\ in most cases), and this directory is not in the path when 1-2-3 runs. Check the installation instructions on how to find a directory that is in your path, and re-install FileControl. Or, you can move the installed copy to an acceptable directory. Lastly you have the option of changing the path statement that is in effect when 1-2-3 runs. You do not need to install a new copy of FILECTRL.WK3 in this situation, only a new copy of CTRL123.EXE. Problem: I saved the file as a WK4 file, and the macros no longer work. Solution: Re-save the file as a WK3 file. Problem: I want to open a file on a floppy drive that is my E drive How do I do so quickly and easily? Answer: Just use the OPEN command on the first menu, and specify the USER_DIRECTORY as the source. Problem: I have more floppies than I have sections. What should I do? Answer: Use the option on menu 2 to add new sections for the floppies. You can have as many sections as you need. Problem: I tried to remove a file from memory but could not do so. Why not? Solution: This is caused by having too many files in memory. To help keep this from happening, remove files as soon as you are done with them. If the problem continues, you will need to add more memory to your computer. Problem: I run commands like Copy, Move, or Rename, and nothing happens and an error message appears saying that the files have not been copied. Solution: The file CTL123.EXE has not been properly installed. It needs to be re-installed. Problem: An error message saying that the directory may not exist appears. Solution: Check the spelling to make sure that the directory is spelled correctly. Run an update on the entire section if you were just trying to update a single section. (This will remove any directories and files that don't exist) If the directory is located on a LAN, make certain you are still connected to the LAN system. Problem: I try to open, move, rename or move a file and it says its not there. Solution: Run an update (menu 1) on the directory in question. Odds are that the file has been deleted, rename, or moved and the listing has not been updated. This happens if you do these activities outside of FileControl. Also, make certain that you don't have the file in memory. We've found that most of the time this won't make a difference, but we have had these options not work on some machines when the file is in memory. Problem: The ALT/CTL-M or the ALT/CTL-G hot key doesn't work when in a different spreadsheet. Solution: You have two macros by the same name. One is in FILECTRL.WK3. The other is in one of the other files that is loaded. To solve, you must delete the macro in the other file. If this is not the case, go to FILECTRL.WK3, and run the hot key there. Then, try it again in another sheet. This will normally cure this problem. It appears that 1-2-3 sometimes "forgets" the keys exist if you are in another sheet. Problem: I ran an update on a section that I use for a particular floppy. However, I put the wrong floppy in and all my documentation is gone, and there is a different list of files for the floppy. Solution: If you have undo active, execute it if you have not done anything else to the worksheet. This will normally reverse an UPDATE. If that doesn't work but you have not saved the file, then you can issue the commands "/ File Retrieve" and retrieve your latest copy of FILECTRL.WK3. Problem: I disobeyed the instructions and modified the macros. Now they don't work. What can I do? Solution: Follow the instructions on installing a new release. This will put a new copy of FILECTRL.WK3 in your default directory and allow you to copy your file lists and documentation to the new copy. The old copy should be erased after this is done. Problem: I tried to set up an icon to be one of the hot keys, but it won't work. Solution: Check the spelling very carefully. Just a single letter off is all that it takes to create this problem. Problem: I renamed FILECTRL.WK3 to something else, and now it doesn't load into memory automatically. And I get an error message when the AUTO123.WK3 file runs. Solution. You can either change the name back, or edit the macro in the AUTO123.WK3 file to specify the new filename. Problem: FILECTRL.WK3 doesn't load automatically when I start up 1-2-3. Solution: You've installed them in a directory that is not your default directory. Check the manual's instructions on where to install. Problem: The screen breaks up when the CTRL123.EXE file is run when I'm in Windows. Solution: You'll need to use a different video driver. Your particular video card and its driver is taking "shortcuts" and thus causing the breakup. Problem: I inserted some rows and copied the section heading using 1-2-3 commands. I ran an update on this new section, and it worked fine. Later, I ran an update on the directory above it, and my new section and all its directories disappeared. What happened? Solution: DO NOT create sections manually. Using the menu option to create them. The cell to the right of the word "DIRECTORY:" has a space and a label in it. What you created didn't. So FileControl thought your section was just a continuation of the directories above. As they weren't, the rows were erased in the update process. The files still exist. Problem: I color coded a file row, but when I ran an update, the color coding disappeared. Solution: To retain color, you must document the file. If its important enough to color code, its important enough to document. Problem: I tried to copy a file that I share on a LAN with a co-worker, but FileControl won't copy it. Solution: When this has happened in our testing, we found that the co- worker had the file loaded into 1-2-3 when we thought he didn't. Unload the file and try again. Problem: When I ran a SORT, it said the arrangement of my information does not correspond to the layout expected. Solution: FileControl expects a blank row between directories, and each group of files to be headed by a directory. And each section must consist of just four rows, with the word "DIRECTORY:" on the top row. Check your spreadsheet and remove any extra rows you may have added. Problem: When all my files get done loading, the hourglass doesn't turn back into an arrow. What can I do? Solution: This is a 1-2-3 bug. It occurs if you have files that open files that open files. We've only had the hourglasses appear in this situation, never when we use the AUTO123.WK3 file to open just FILECTRL.WK3. However, there is a simple solution. Just click on the icon that takes you another screen. Then click on the icon that takes you back to where you were. The hourglass then becomes an arrow. Problem: I saved FILECTRL.WK3 to a different directory, and now the commands don't work. Solution: You can solve your problem several ways: - Press ALT-F3 and specify "\0" as the macro to be run (that's a zero not the letter O) - Retrieve the saved file. That automatically runs the \0 macro. What happened is that when you first opened FILECTRL.WK3, it recorded the current location of the file. Since you saved it to a different location, the location stored in FILECTRL.WK3 became incorrect. By retrieving the file or running the "\0" macro, this updates this information and the macros work.