 The magazine of the Melbourne PC User Group Members' tips 'n' tricks Ron Taylor |
Batch File Accelerator
Michael Palmer
When you get up to speed with DOS you will, no doubt, start writing some batch files to ease your familiar day-to-day operations. These are good to write and the more you get into batch files the longer and more complex your batch files will become - nothing wrong with that at all!
What you will find, when you write a reasonably complex batch file, for example a Menuing system which calls other batch files, is that DOS will have you tapping your fingers on the desk while it patiently reads each line and executes your instructions.
A programmer, Doug Boling, has written a nice litle utility which will help you out. This is called BAT2EXEC and was written for PC Magazine as a part of their series of utilities which are put out as freeware.
The version I have is called BAT2EX15.ZIP that I picked up while browsing through the plaza.aarnet.edu.au ftp site on the Internet. The operation is simplicity itself. Just unzip the file and you will find a .EXE file and a .DOC file. To compile your .BAT file to a .COM file just use the following syntax:
BAT2EXEC FILE.BAT
(where FILE.BAT is your batch file)
That's it! BAT2EXEC will produce an executable .COM file with the name FILE.COM (same name as your batch file). If it can't find your batch file or it finds a line it can't understand in your batch file it will produce an error message.
Now a couple of words of warning. Don't use it on your AUTOEXEC.BAT file otherwise COMMAND.COM will have a heart attack as it needs a genuine batch file to read. Also don't compile batch files which use TSR utilities as memory conflicts can arise. All this and more is covered in Doug Boling's notes and as he says it is not worthwhile to compile two-line batch files as these are best left in their ASCII format. However, for long batch files it is a gem and the price is definitely right!
Sharing in WinWord v6
Ron Taylor
Many users find when installing Word for Windows version 6.0 that it will complain loudly if SHARE.EXE is not loaded. Note: This only occurs if running Windows 3.1 or Windows for Workgroups 3.1 and does not apply to either the WFW 3.11 version or Winword 6.0a.
A file-sharing device is required by applications that support the advanced OLE2 (Object Linking & Embedding v2). Windows for Workgroups 3.11 includes a driver VSHARE.386 that does this and eliminates the need for the DOS equivalent SHARE.EXE.
Thanks to Microsoft who regularly release what they call "Application Notes" (in effect, updates and bug fixes), there is a version of VSHARE.386 available for the older Windows so you can at last dump SHARE.EXE from your AUTOEXEC.BAT or CONFIG.SYS. Look out for filename WW1000.EXE (a self-extracting archive) and follow the instructions enclosed for installation.
By the way, don't forget to get hold of another MS App Note - file name WW1138.EXE. This is an update for our old friend Calculator which normally lives in the Accessories Group. It seems that good old Calculator didn't do its job too well under some circumstances, nothing major but thankfully MS have now got the fix out for those who need it.
And where do you get hold of these files if you need them? Well, I suppose you could try Microsoft (you may enjoy the music when put on hold as your STD phone bill ticks away), or you could hunt around for them on Compuserve or the Internet... Better still, why not just dial up the BBS of your favourite PC User Group? - you'll easily find them there!
Excel with Excel
Lisa Byrne
Filespecs in the Footer
Using MS Excel, you can easily create a macro that will update the pathname and filename of a spreadsheet in it's footer every time it is opened. Create a macro with the text from Figure 1 in cells A1 to A5:
|
A1 is the macro name. A2 gets the name of the currently active sheet. A3 gets the path name A4 puts the path name and file name into the footer. |
|
|
Now hightlight cell A1, and use Formula Define Name to define this as a command. In cell A4, the "&C" means put the path and filename at the centre of the footer. You can change this to "&L" for left or "&R" for right if you want.
Save the macro sheet as FOOTER.XLM (or whatever). Now the macro has been set up, all you need to do is define the name Auto_Open in any sheet that you want the footer in.
To do that, open the sheet and select Formula, Define Name. In the Name box, type "Auto_Open" (without the quotes). In the Refers To box, type "FOOTER.XLM!Path_Into_Footer" (again without the quotes). Now, anytime you open that sheet, the macro will look up the current path name and filename, and put it into the footer.
That macro will only work in Excel 4.0. For Excel 5.0, the macro in Figure 2 will do the trick.
|
|
Time Calculations (1)
Adding up minutes and seconds can be confusing in spreadsheets. In Excel4 when you go into Numbers under Format it insists that you also put in the hours so that it can add properly.
You can enter the times using decimal format instead of time format and then add them together using the TIME function. If, for example, you entered 50.3 in cell A1 and 50 in cell A2, the following formula will convert them to hours and minutes and add them together, giving 1:40:30 as the result:
=TIME(0,INT(A1),(A1-INT(A1))*100) + TIME(0,INT(A2),(A2-INT(A2))*100)
You will need to format the cell containing the formula as hh:mm:ss. In Excel 5.0 you can use [mm]:ss for a result of 100:30
Time Calculations (2)
Ever had to add up times that run over 24 hours in Excel 4.0? When you add two cells with, for example, 23:00 and 2:00, the result should be 25:00 right? Wrong! The answer you will get is 1:00 because Excel (understandably) converts times into 24 hour format.
To get around this limitation, add the times as normal and perform the following function on the result. This formula also works with negative times:
=(2*TRUNC(24*A1)+72*A1)/5
where A1 is the cell containing the incorrectly shown time, just change both occurences of "A1" to the cell your result is in. This is only valid for Excel 4.0. In Excel 5.0, you can use square brackets (eg. [hh]:mm) to format times over 24 hours.
How and why it works
First, you must understand how Excel works with time values. Any time or date is converted to a serial number. The integer part of the serial number represents the date, and the fractional part represents the time. The fraction part is just the time divided by 24. For example, 12:00 noon would be converted to 0.5 (12 divided by 24).
Let's take the time, 25:30 (displayed as 1:30) in cell A1. 25:30 converts to the serial number 1.0625. If we create a formula in cell A2 to multiply this serial number by 24, we get 25.5 meaning 25 1/2 hours. We now have the correct time, but it's in decimal format.
Now you could just as easily convert the fraction part back to a percentage of 60 minutes and then add it to the integer part of the result, i.e.
=((A2-INT(A2))/100*60) + INT(A2)
to give 25.3 but that would require another cell to work in (a bit messy for my taste).
This is the tricky bit... I wanted to divide the whole lot by 5 because it's an easy number to work with when you're talking percentages. You'll see what I mean in a minute.
60 minutes divided by 30 minutes equals .5 of an hour which is .3 in decimal (time-speak). So we have to multiply the serial number by 3*24 which is 72. This is the key to the conversion. 72*A1 gives us the correct fractional part to be divided by 5 (the .5 of an hour), so we don't need the fractional part from the 24*A1. The TRUNC function gets rid of this.
Once we've done that, we have to bolster up the first part of the formula to five times the value so we can divide by 5. We now have 72*A1 which is three times the value plus five times the fraction, and
TRUNC(24*A1)
which is one times the value minus the extra fraction, so we need to multiply the first part by two to get five times the value. Then we divide the whole lot by five and Bob's your Aunty!
Reprinted from the May 1995 issue of PC Update, the magazine of Melbourne PC User Group, Australia
