Category : BASIC Source Code
Archive   : 123QB11.ZIP
Filename : 123WRITE.DOC

OVERVIEW 1
FILES INCLUDED WITH 123QB11.ZIP 1
LICENSE 2
DISTRIBUTION 2
REGISTRATION 2
SUPPORT 2
REQUIREMENTS 4
GENERATING THE 123-WRITE LIBRARIES 4
COMPILING 5
CALLING SEQUENCE 6
SOME GUIDELINES 7
FINDING YOUR WAY AROUND THE SPREADSHEET 8
LotusCellFunction 9
LotusCellMath 10
LotusClose 11
LotusCol 12
LotusColFunction 13
LotusConstMath 14
LotusDate 15
LotusDown 16
LotusFileAppend 17
LotusFileOpen 19
LotusGetLoc 20
LotusGlobal 21
LotusHeader 22
LotusLeft 23
LotusLineFeed 24
LotusMCellMath 25
LotusPrintBorders 27
LotusPrintMargins 28
LotusPrintRange 29
LotusPrintSetup 30
LotusPrintTitle 31
LotusRange 32
LotusRight 33
LotusRowFunction 34
LotusSetCellFormat 35
LotusSetCol 36
LotusSetCols 37
LotusSetDateForm 38
LotusSetLoc 39
LotusSetProtect 40
LotusSetStrFormat 41
LotusSetTimeForm 42
LotusSortKey 43
LotusSortRange 44
LotusTime 45
LotusToday 46
LotusUp 47
LotusWriteErr 48
LotusWriteInt 49
LotusWriteNum 49
LotusWriteStr 50
ERRORS 51



i





123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


OVERVIEW

123-Write is a collection of sub programs to write a Lotus
1-2-3 file from within a QuickBASIC 4.0+ or BASCOM 6.0+ program.
Not all of 1-2-3's functions and operations are available, yet I
feel that I've included some of the most frequently needed.

In the past I've always written programs that allow the user
to specify where report output should go, the screen, printer a
print image file, etc. These routines were an outgrowth of two
needs. The first one was just wanting to "dress up" my code a
little more. What could be sharper then writing a report's output
directly to a 1-2-3 spreadsheet file? The second, and more
important reason, was all the work involved in 1-2-3 to import,
transfer and work with data from another program. One commercial
package offers a "Lotus 1-2-3" export option on their report
menu. When I needed just that ability once I was a more then a
little disappointed to find the output file nothing more then a
comma delimited sequential file. Importing to Lotus was simple,
but dates became strings, and all the data had to be shifted
around within Lotus to be of any use.

123-Write will provide you with the ability to take that
comma delimited file and convert it directly into a formatted
worksheet for example. Read and write the data just the way you
need it. Generate an up to date spreadsheet from your own BASIC
data files whenever needed. You won't have to write out an ASCII
sequential file, and then write Lotus 1-2-3 Macros to read the
data file into your worksheet.


FILES INCLUDED in the 123QB11.ZIP:

123WRT_A.OBJ For BC 6.0 with /fpa switch.
123WRT_I.OBJ For BC 6.0 with /fpi, QB 4.0/4.5 Linking.
MAKELIB.BAT Generate the .QLB and .LIB files.
123WRITE.DOC Complete documentation for version 1.1
123WRITE.BI An ASCII include file that DECLARES all the
123-Write routines.
DEMO1.DAT Dummy data file for demo program.
123DEMO.BAS Demonstration of a few of the routines.
example: QB 123DEMO /L 123WRITE.QLB

(Before running 123DEMO make sure the .DAT file is in the default
directory. A sample spreadsheet named DEMO.WKS will be written.
You can specify the path and a different name for the spreadsheet
file however.)






1




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought



LICENSE

Non registered users are granted a license to use 123-Write for a
limited evaluation period, for your own noncommercial use. Use
of the routines in any software that you sell, generate in the
course of your employment, or after a reasonable evaluation
period (30 days) requires registering the copy.

DISTRIBUTION

You may copy and freely distribute 123-Write, as long as:

No fee is charged. (Recognized user groups and clubs may
charge a nominal fee for expenses.)

All the files included with this documentation are dis-
tributed intact and without any modification(s).

WARRANTY


This software is distributed AS IS without any warranty,
express or implied, including but not limited to fitness for a
particular purpose.

REGISTRATION

If you use the 123-Write routines within any application
that you distribute, or in the course of your employment, regis-
tration at $20.00 is mandatory. If you find 123-Write convenient
to use, if you find 123-Write a help to your programing, a $20.00
registration is expected. With each registration you will be
listed to receive a diskette of the next release of 123-Write.

SUPPORT

Questions will be answered when asked. Fastest method for
non-registered users may be via a call to The Bounty BBS in
Freehold NJ. 201-431-4088 N81 9600/2400/1200 baud 24 hours a
day. Address your question to Tom Vought and I'll reply as soon
as possible. US Mail to the address on the registration form
will also work.


A registration form for your use follows.






2




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought

Make checks payable and mail To:

Thomas J. Vought
5 John Street
Morganville, NJ 07751

Registration:


Name: _____________________________________________


Company Name: _____________________________________________

Mailing Address

Street: _____________________________________________


Town, State & Zip: _____________________________________________


Phone: ____________________________________________


Do you have a modem? ______________

What functions, @formulas, or other esoteric stuff would you like
to see included in any future releases of 123-Write?






Comments?:





Documentation comments:











3




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


OK, I think I have all that stuff about registration out of the
way. Only one more item. Routines are added based on user
suggestions, registered or not.

Requirements:

123-Write was developed and tested using MicroSoft's Quick-
Basic version 4.5 and MicroSoft's Basic Compiler version 6.0.
With one minor exception, the worksheet files written are compat-
ible with versions 1a and 2 of Lotus 1-2-3. The one exception is
the LotusTime routine, which writes a version 2 time format to
the worksheet cell. Lotus 1-2-3 version 1a will read the file
and display a (?) as the format designator, since there were no
time formats available in version 1A.

The object files included can be linked in with your object
file as they are supplied. Creating a quick library and a linker
library is performed by the MAKELIB.BAT file included. The
syntax for MAKELIB is:

MAKELIB version

Where version determines which BASIC supplied library files
are used to create the Quick Library, 123WRITE.QLB. Find the
BQLBxx.LIB file you have and place it in the same directory as
the 123WRT_?.OBJ files. Make sure that LIB.EXE and LINK.EXE are
available through your path, or are in the same directory. The
BQLBxx.LIB file dictates which version of QuickBASIC the Quick
Library will be compatible with.

use: for: Needs:

40 (QuickBASIC 4.0) BQLB40.LIB

41 (QuickBASIC 4.0a) BQLB41.LIB

45 (QuickBASIC 4.5) BQLB45.LIB

6 (BASCOM 6.0) BQLB41.LIB Note: uses the /FPI
(QB 4.0a) compiled OBJ file
for 123WRITE.LIB.
6A (BASCOM 6.0) BQLB41.LIB Note: uses the /FPA
(QB 4.0a) compiled OBJ file
for 123WRITE.LIB.








4




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought



If 123WRITE.LIB or 123WRITE.QLB already exist in the current
directory, the batch file will stop and prompt you to remove
them. The reason for this is to allow you to tailor, if needed,
the library files to your working environment. For example, I
use BASCOM version 6.0 for compiling, and QuickBASIC 4.5 for
development. Therefore, I would run the MAKELIB batch program
with 45 first and move the 123WRITE.QLB aside for use with Quick-
BASIC. Then I would delete the 123WRITE.LIB file and run MAKELIB
again with 6 to generate an in-line floating point
instruction .LIB file for use with the linker. If you use BASCOM
6.0 and compile with the alternate math option, you may want to
use the 6A option to create an /FPA compatible .LIB file.
(The .QLB file will still use the in-line floating point instruc-
tion code though.)

Take a look at the MAKELIB.BAT file and feel free to modify
it if needed to your particular needs. Just don't pass on the
123-Write package with MAKELIB.BAT modified! You can of course
add the 123-Write OBJ files to your own .LIB and .QLB libraries
if you desire.

The MAKELIB.BAT program creates two temporary files, ~LIBRSP
and ~LINKRSP which are the LIB.EXE and LINK.EXE 'response' files
respectively. They are deleted when the batch file finishes.


COMPILING with 123WRITE:

When compiling to an .EXE file, it's best to compile from
the DOS command line, NOT from within the QuickBASIC environ-
ment. There is a switch that QB throws into the LINK command
line that seems to cause errors and trashed EXE's.

If you're not using Bascom 6.0 and get a "BCOM60ER.LIB not
found" message from LINK, substitute the proper QuickBASIC li-
brary name when prompted. BCOM40.LIB or BCOM45.LIB will work
fine for QuickBASIC users.

The 123-Write version 1.1 routines are written in BASIC,
and as a result, when the .OBJ files were created, the compiler
added the "Default Run Time Library" name of BCOM60AR to the
123WRT_A.OBJ and BCOM60ER to the 123WRT_I.OBJ file. If the not
found errors cause problems, you can add the /NOD (no default LIB
search) switch when LINKing. Then enter BCOM40 or BCOM45, a
plus (+) sign and 123WRITE in response to the LINKer's Libraries
[LIB] prompt. If you're really daring, copy your BCOMxx.LIB over
to a file named BCOM60ER.LIB. Course you have to keep things
straight in your head.



5




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought

Sequence of 123-Write calls:

There can only be one worksheet file open for writing at a
time. The routines need to be called in the proper sequence:

1. Open the file, of course.
Use either:
LotusFileOpen(filename$) <---Preferred
Supply a fully qualified file name with
the drive and path if desired. An existing
file will be over-written.
Or
LotusHeader(fileHandle%)
If you open a file for BINARY outside these
routines. (For network access use as an
example.) The file will be over-written if
there's anything in it.
Or
LotusFileAppend(filename$)
To open an existing worksheet file to add
on to it.

2. Write any column width settings desired.
Use either:
LotusSetCol(column%,colWidth%)
for a single column width setting.
Or
LotusSetCols(columnWidths%())
for a variety of columns.

3. Set the cell format for strings and numeric data if
other then the defaults of left-aligned and fixed
with two decimal places.

4. Set the starting column and row, if other then 0,0 (A1)

5. Write the data.

6. Close the file.
CALL LotusClose

Closing the file is a must. If it's not done via a call to
LotusClose, you'll get a "Part of File Missing" error from Lotus
when loading the spreadsheet. Lotus needs certain information
when it loads a worksheet, and the information can't be written
to the file by 123-Write unless you call the LotusClose sub-
program to close the file. If a worksheet file is closed in
another manner, all the data will be there, but it's kind of
disconcerting to see the error message pop up when the worksheet
is loaded by 1-2-3.



6




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


The worksheet file is opened and manipulated just like any
other BASIC file. One important item to remember is: DO NOT USE
a CLOSE (all) statement in your program BEFORE calling Lotus-
Close. If you use a general CLOSE instruction (close all files)
then the spreadsheet file will be closed too, and a subsequent
call to LotusClose will cause an error. Call LotusClose BEFORE
you invoke a general CLOSE instruction. (Individual files you
open can be closed of course, via a CLOSE #1 or CLOSE #inputFile
instruction.) There is no way I can find in BASIC to check that
the file is still open before attempting any writes, so the
burden is on your shoulders to debug properly.


Things to remember when coding calls:

All CALLs that take numeric parameters use 2 byte signed
integers, except for LotusWriteNum, which takes one double preci-
sion 8 byte real value, and LotusConstMath where the constant is
also a double precision 8 byte real variable.

All cell references in Lotus are 0 based. Column A is
column 0, and row 1 is row 0. Clear as mud right? Well, just
remember to subtract one from what you think of the row number
as. If you count columns on your fingers like I do, the first
finger is 'B'. All the routines that need column and row numbers
use the Lotus zero based references. LotusCol is a function
(remember to DECLARE it as a function in your program if you use
it.) that will return the zero based column number when called
with a one or two byte string. See the entry for LotusCol for an
example. It relieves the strain of counting on your fingers.

Now an important point. The logic in these routines (if
any) is based on a left to right, top to bottom sequence. This
simulates the printing of a report. Every time a routine is
called that writes data to a cell, the cell pointer is increased
by one column, the row stays the same. If you should attempt to
write a cell beyond the 255th, (IV (?)) the routines will incre-
ment the row and return to column 0(A). When printing to the
printer or screen, you allow for line feeds, the LotusLineFeed
routine serves the same purpose. The row is increased and the
column returns to 0.

Another point is when using the 'cursor movement' routines,
LotusLeft/Right/Up/Down. If you have just written to cell A5
(0-4) and call LotusUp(4) to move up to row 0, you will be in
column B (1). The cell pointer was moved one column to the right
after A5 was written. Some allowances for the column increments
upon cell writing have to be calculated in.




7




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought

Where Am I in this 'SpreadSheet' anyway?

The answer to that depends upon how the spreadsheet file was
opened. If it's a new spreadsheet file (LotusFileOpen or Lotus-
Header) then the column pointer is set to column 0 (A) and the
row pointer set to row 0 (1) when the file is opened. The {HOME}
position therefore, is the first cell written, then 1,0 (B1),
then 2,0 (C1). A CALL to LotusLineFeed at this point would mean
that the next cell written would be 0,1 (A2), then 1,1 (B2) etc.
It takes a little getting used to, but it works well.

If the file was opened with LotusFileAppend, then the cell
pointer will be set to the {end}{home} position. This cell is
the bottom right corner of the spreadsheet. It may very well
contain data, so the first thing after calling LotusFileAppend
you need to do is position the cell pointer to where you want the
next stream of data to be written.

There are routines to get the location of the cell pointer
and to set the location of the cell pointer. First
LotusGetLoc(whereAmIcol%, whereAmIrow%) returns the current cell
pointer column and row coordinates, and a call to
LotusSetLoc(nextCol%, nextRow%) will adjust the cell pointer.

Three reported bugs in version 1.0 are corrected in version
1.1. The LotusDate routine choked on a date delimited by dashes.
That has been corrected so a dash "-" or a slash "/" is OK. What
can I say? I blew it. I even used dashes in the documentation.

The second was a MAJOR bug in the LotusWriteStr routine.
The code attempted to test the second character of the string
passed to the routine {ie: MID$(data$,2,1)} when a test of the
string's length hadn't verified it was at least two bytes long.
A real easy one, and it shouldn't have been there, but the easy
bugs are the ones you don't (at least I miss them) plan and test
for. My apologies on this one.

The last one was an annoyance, the string passed to Lotus-
WriteStr was modified if it did not contain a leading label
prefix character. The routine would prefix the passed data
string with the default label prefix character and that string
got returned modified. The LotusWriteStr routine now manipulates
the string internally and won't modify the string passed to it.



Detailed descriptions of each routine follow in alpha-
betical order. All routines begin with Lotus---





8




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusCellFunction

SYNTAX: LotusCellFunction(col%, row%, funcNumb%)

col% is the column number of the cell the function
is to be performed on.
row% is the row number of the cell the function
is to be performed on.
funcNumb% is set to one of the following:

1 @ABS the absolute value of the
referenced cell.
2 @INT the integer portion of the
referenced cell.
3 @SQRT the square root of the referenced
cell.
4 @LOG log base 10 of the referenced cell.
5 @LN the natural log (log base e) of the
referenced cell.
6 @SIN the sine of the referenced cell.
7 @COS the cosine of the referenced cell.
8 @TAN the tangent of the referenced cell.
9 @ATAN the arc-tangent of the referenced
cell.
10 @ASIN the arc-sine of the referenced
cell whose value should be between -1
and 1. (Lotus will display ERR other-
wise)
11 @ACOS the arc-cosine of the referenced
cell whose value should be between -1
and 1. (Lotus will display ERR other-
wise)
12 @EXP the value e (approximately
2.7182818) to a power specified by the
referenced cell.

LotusCellFunction provides the Lotus functions that operate on
only one cell at a time. Any function numbers outside the range
1 through 12 will result in the target cell being tagged with
@ERR.

Multiple functions can't be combined, the alternative is to write
the components of the multiple functions to out of the way cells,
and operate on them in steps.

EXAMPLE: CALL LotusCellFunction (2,5,3)

Will result in: @SQRT(C4) being written to the current cell.




9




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusCellMath

SYNTAX: LotusCellMath(col1%, row1%, col2%, row2%, oper%)

col1% is the column number of the 1st cell the
function is to be performed on.
row1% is the row number of the 1st cell the
function is to be performed on.
col2% is the column number of the 2nd cell the
function is to be performed on.
row2% is the row number of the 2nd cell the
function is to be performed on.
oper% is one of the following:

1 add cell 1 and cell 2.
2 subtract cell 2 from cell 1.
3 multiplication of cells 1 and 2.
4 Division of cell 1 by cell 2.
5 Cell 1 raised to the power of cell 2.

LotusCellMath provides simple math operations on two cells. Any
function numbers outside the range 1 through 5 will result in the
target cell being tagged with @ERR.

EXAMPLE: col1 = 0 'A
row1 = 2 '3
col2 = 8 'I
row2 = 5 '6
oper = 3 'Multiply

CALL LotusCellMath(col1%, row1%, col2%, row2%, oper%)

Will multiply A3 by I6 and place the formula in the current cell.



















10




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusClose

SYNTAX: LotusClose


Closes the currently open spreadsheet file after writing the
Lotus end of file marker and updating the worksheet range. The
worksheet range is the cell that results from the {END}{HOME} key
presses in Lotus.

A general CLOSE statement in your program will close the spread-
sheet file also. Use CLOSE # statements if necessary, or be
sure to call this routine before you use a blanket CLOSE state-
ment in your program.

Note that all the routines in this library keep track of the
spreadsheet file handle internally. LotusClose resets the handle
to 0 and all the routines that write to the file naturally check
for a valid handle before an attempt is made to write data to the
file. Calling a routine when there is no file open, or the file
wasn't opened via LotusHeader or LotusFileOpen, will not cause a
crash because the internal handle is checked. However, if you
close the file outside of the 123-Write routine, (example, open-
ing the file outside, using LotusHeader and then closing it
outside of the 123-Write routine.) will result in three things
becoming possible/probable.

1. Subsequent calls to LotusFileOpen and LotusHeader will fail
because the internal handle still indicates a file open. (The
handle was set in LotusHeader.)

2. Any calls to a routine that attempts to write data to the
spreadsheet file will bomb and dump you back to the operating
system, if they are inadvertently called after the file is
closed. The handle appears valid, so an attempt is made to write
data to the file, and BASIC will generate an error.

3. Lotus will beep and issue a "Part of file missing." error
when the spreadsheet loads. Definitely not a reassuring happen-
stance.


Very little error checking is done in the 123-Write routines.
It's your job to debug your mainline code so the file is properly
opened and closed.

EXAMPLE: CALL LotusClose





11




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusCol

SYNTAX: LotusCol (column$)

column$ is a one or two byte string.


LotusCol is a FUNCTION, and as such must be declared in your
program before any calls are made to it. Otherwise the compiler
will treat it as an array reference.

LotusCol returns the zero based column number for the corre-
sponding column$, if column$ is a valid representation of a Lotus
column. It must be between "A" and "IV". If the string is
invalid, LotusCol will return a value of -1.

The reason LotusCol is a FUNCTION rather than a sub program,
is so that it can be used in calls that require an integer column
number. You may notice I make liberal use of this function in
the demo program. In reality however, I would use this function
only where a real variable would come into use. The INPUT of a
column letter in the demo is an example. Otherwise as I debug,
I'd stick a stop statement after the function call, get it's
result and use that value in the finished code for speed.


EXAMPLE:

x = LotusCol ("AB")
' x should be equal to 27. (A-Z) = 26
' + (A-B) = 2
' - Zero Base = -1
' -----
' 27

See examples under LotusSetLoc and LotusSetCol also.
















12




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusColFunction

SYNTAX: LotusColFunction (startRow%, endRow%, oper%)

startRow% represents the first row to operate on.

endRow% represents the last row to operate on.

oper% is set to one of the following:

1. @SUM the sum of the cells.
2. @AVG the average of the cells.
3. @COUNT the count of cells with
valid data.
4. @MIN the lowest value in the range.
5. @MAX the highest value in the range.

LotusColFunction is a quick method of writing common columnar
functions to a cell, or a series of cells. The routine assumes
the column to operate on is the current column, so all that's
needed in the call is the first row to include and the last row
to include. You can set the cell pointer with LotusSetLoc if
needed.

An example is probably the best way to illustrate. Lets say
you're writing a spreadsheet where the first three rows are
titles and column headings etc. The first valid data row is 3,
and you have 4 columns of data, 0 through 3 (A through D). The
number of rows of data vary based on the database file you're
reading, so you don't have a fixed ending row when the program is
run. Code would look something like this:

FOR i = 1 TO LOF(dataFile%) \ LEN(dataRecord%)
.
...writing a row loop...
.
CALL LotusLineFeed 'Get set for next row.
NEXT i

'after the last data item is written, your row pointer
'is one below the last row of data, and your column
'will be 0 (A) so:

CALL LotusGetLoc(currentCol%, currentRow%)
endRow% = currentRow% - 1

FOR i = 0 TO 3
CALL LotusColFunction(3, endRow%, 1) '@SUM
NEXT



13




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusConstMath

SYNTAX: LotusConstMath(col%, row%, constVal#, oper%)

col% is the column number of the cell the math
is to be performed on.
row% is the row number of the cell the math
is to be performed on.
constVal# is a double precision (8 byte real) value
oper% is one of the following:

1 add cell and supplied constant.
2 subtract the constant from cell.
3 multiplication of cell by the constant.
4 division of cell by the constant.
5 Cell raised to the power of constant.

LotusConstMath writes a simple math calculation using a cell
address and a constant you supply. Note that the constant you
pass must be an 8 byte double precision real value.

Don't confuse the use of the term constant with BASIC's use
(CONST), a constant in the Lotus sense is a hard number that
won't change. See the example below for how the result would look
in Lotus 1-2-3.

Operation values outside the range of 1 through 5 will result in
the target cell being marked @ERR.


EXAMPLE: CALL LotusConstMath (0, 3, CDBL(5), 3)

will multiply the value of cell A2 by 5.
The spreadsheet formula would appear as:
+A2*5

















14




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusDate

SYNTAX: LotusDate(dat$)

dat$ is an 8 or 10 byte date string.


Writes the Lotus date value of the date supplied in dat$ to the
current cell. The value is adjusted to conform with the errone-
ous values 1-2-3 uses. The date string passed should be between
01-01-1900 and 12-31-2099.

Useful for doing those 30-60-90 day aging schedules everyone
seems to need.

Only two date formats will convert properly, an eight byte
mm-dd-yy or a ten byte mm-dd-yyyy (Basic's) format. The only
conversion that is done is to an 8 byte date is to prefix the
year with a "19" or "20" depending on the date the program is
run. If the date string passed to the routine cannot be convert-
ed to a numeric value, it is written to the cell as a label
instead of a number.

If a default date format has not been set via a call to LotusSet-
DateForm, the target cell is formatted to display the date in D1
format. (dd-mmm-yy) which requires a column width of roughly 10
characters. See the LotusSetCol & LotusSetCols routines for
information on setting column widths.


EXAMPLE: 'Date stamp the worksheet file:
CALL LotusWriteStr("As of:")
CALL LotusDate(DATE$)



















15




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusDown

SYNTAX: LotusDown(count%)

count% is the number of rows.


LotusDown is the equivalent of hitting the down arrow key in
Lotus. The next row to be written will be increased by the
number of rows the routine is called with. To maintain compati-
bility with Lotus 1-2-3 ver 1a, the maximum row number is 2047.
Attempts to 'cursor' down beyond row 2047 will leave the cursor
in row 2047.


EXAMPLE: move% = 5
CALL LotusDown(move%)
'The next cell to be written will be 5 below
the
'current row, in the same column.
































16




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusFileAppend

SYNTAX: LotusFileAppend(fileName$, ecode%)

fileName$ is a fully qualified file name, that
may include a drive and path if needed.

ecode% is returned to indicate an error.
-1 File specified does not exist.
-2 A spreadsheet file is already open.
-3 The specified file is not a Lotus
123 version 1a, or 2.0+ file.

Use LotusFileAppend to open an existing spreadsheet file and
add on to it.

Lotus files need the {end}{home} cell position written in
the file, so 123-Write tracks this cell continuously. When open-
ing a spreadsheet file to append data to it, this routine will
read as much of the existing file to determine the {end}{home}
cell as needed, then gets ready to write any data you wish. The
updated {end}{home} cell is written to the file when LotusClose
is called.

The next cell to be written after LotusFileAppend is the
{end}{home} cell. The row and column coordinates are not
changed, and the {end}{home} cell is not changed, until you actu-
ally write data to the file. The {end}{home} cell will most
likely have valid data in it, and the first thing you should do
after a call to this routine is position the cell pointer with a
call to LotusSetLoc or LotusLineFeed before writing any data to
the file.

Now a caution, although a minor one. It is OK to open a
file, position the cell pointer to a previously written cell and
'over-write' the data in that cell. The thing to keep in mind
though, is that the cell is not physically over written in the
file, but that another entry for that cell is added to the end of
the file. This causes for problems with Lotus, but I'll throw in
an example anyway.

Say you have a worksheet that has your Year to Date Widget
sales in cell 'A5'. Your application can determine the year to
date sales from anywhere of course, and you want it to open the
spreadsheet and update cell 'A5' whenever it is run. Let's say
your application is run once a week. You open the spreadsheet
for append with this routine, call LotusSetLoc to set the cell
pointer to 'A5' and call LotusWriteNum to write the year to date
widget sales figure. Then you close the file and go on your way.



17





123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


Now let's jump ahead to December when the New Year's Bonus
subject comes up, which is based on year to date widget sales.
Amazingly, this is the only time the spreadsheet in question gets
loaded. What will happen, is that Lotus will assign a value to
cell 'A5' the first time it comes across it in the file, then
change the 'A5' value the next time it reads it (your first
update) and continue on, replacing the 'A5' value 52 times. Your
disk file also has 52 entries in it for cell 'A5' which is a
physical consideration. What you will end up with, is the last
year to date widget sales figure in 'A5' which is fine.

Now the reason I think it's not a major consideration is
that when the spreadsheet is saved from within Lotus, only the
current (last) value of 'A5' would be saved. The file size and
loading time would revert back to normal. Few spreadsheets are
ever only read into Lotus and not saved. On the other hand, if
there were a large number of cells being over-written regularly,
and the spreadsheet was only for some type of reference, things
may get out of hand. A hint would be to write a macro in an out
of the way place, named '\0' that would save the file as soon as
it's loaded into 1-2-3. The LotusRange, cell pointer positioning
routines and LotusWriteStr routines will allow you to write a
macro for just this purpose.




























18




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusFileOpen

SYNTAX: LotusFileOpen(fileName$)

fileName$ is a fully qualified file name, that
may include a drive and path if needed.

Preferred method to open the worksheet file for output. Pass the
routine a valid file name which can include a drive and path if
needed. If an invalid file name is passed, you guessed it, drops
to the operating system with a crash. No error checking or
parsing of the file name (except for an extension) is done.
You're in charge here.

You can pass any file name you wish. If the file name does not
include an extension, ".WKS" is used by default. This routine
and LotusHeader write the initialization data to the spreadsheet
file. The initialization identifies the worksheet file as a
version 1a file, and includes only the data that 1a would write.
This does not seem to pose any problem with a ".WK1" extension.
When Lotus 2.0 loads a ".WK1" file with a 1a signature, no
errors are generated, so I assume no extension to file type check
is done by 2.0.

If there is already an open worksheet file, any calls to the
LotusFileOpen will fail without any indication being passed back.
Use LotusClose only to close the spreadsheet file, after which
another call to LotusFileOpen can be made to open another spread-
sheet file.

Now the reason why there are two routines for opening a spread-
sheet file. This routine generates a file handle for internal
use that is 123 plus the next available file handle. For exam-
ple, if your program has two files opened when LotusFileOpen is
called, the internal handle will be 123+3 or 126. I don't sup-
pose there will ever be a situation where a conflict occurs.
Some other routines may use a base plus handle like this one, or
a fixed handle up in the 100's (99 seems popular) where a dupli-
cation could arise. The LotusHeader sub program provides an
alternative though, in that it requires you to supply the file
handle, that way you could open the file with access clauses and
locks for use in a network environment.

EXAMPLE: CALL LotusFileOpen("D:\LOTUS\TESTFILE.WK1")

will override the default "WKS" extension, and
write the file to drive D, in the sub directory
named LOTUS.




19




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusGetLoc

SYNTAX: LotusGetLoc(whatCol%, whatRow%)


whatCol% will be returned equal to the next
column number that is to be written.

whatRow% will be returned equal to the next
row number to be written.

Not a lot to say about this one, returns the row and column
numbers of the next cell to be written. Just remember that the
column and row coordinates are always 0 based, column A = 0 and
row 1 = 0. See also the LotusSetLoc routine which allows you to
set the row and column for the next write.


EXAMPLE: CALL LotusGetLocation(currentCol%, currentRow%)
IF (currentRow% + 1) MOD 20 = 0 THEN CALL pageHeader

If you had a routine called pageHeader that you
wanted to call for each 20 row "screen page" this
would do it. (I think.)




























20




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusGlobal

SYNTAX: LotusGlobal(item%, switch%)

item% is a value from 1 to 5.

switch% adjusts the specified item.


When item% Worksheet global switch%
is: item effected: value:

1 Calculation iterations. 1 through 255 1 *

2 Auto recalculation. 0 = Off (Manual)
otherwise On (Automatic) *

3 Calculation order. 0 = natural *
1 = column-wise
2 = row-wise

4 Unformatted Printing. 0 = Formatted print *
otherwise Unformatted print

5 Global worksheet protection 0 = Unprotected *
otherwise protection On


This routine will allow you to change any of the default
global settings of the worksheet. If the file is not opened via
LotusFileAppend, the defaults for each item are marked with the *
asterisks above. The qualifier about how the file is opened is
added because these settings could already be set in the file
that is being appended to. To speed processing, the settings
above are not read when LotusFileAppend is used, however, they
can be added to a file opened in append mode. The last global
switch read will take precedence.

A note is in order regarding protection. As a default,
both Lotus 1-2-3 and 123-Write will write each cell as a protect-
ed cell, but the protection won't be enabled unless it is specif-
ically turned on, in Lotus the /Worksheet /Global /Protection
/Enable series of commands will do it, or in 123-Write a call to
this routine. Cell protection can be disabled for a series of
cells by using the LotusSetProtect routine, which will toggle
cell protection off and on.






21




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusHeader

SYNTAX: LotusHeader(fileHandle%)

fileHandle% is the file number you opened the file
with, in BINARY mode.

RETURNS: fileHandle% -1 the fileHandle you passed is less
then or equal to zero.
-2 there is a spreadsheet file already
open, that hasn't been closed with
LotusClose.
-3 The fileHandle you passed is open,
but not in BINARY mode.

LotusHeader is an alternative routine to LotusFileOpen. There's
more work involved here on your end, but if you like tracking
file handles yourself, here you go. The LotusFileOpen routine
uses DOS 2.0+ file handling, so no file locking can be done in a
network environment. If your program is going to run on a net-
work with DOS 3.1+, and you want to include file locking, this
method will have to be used to open the spreadsheet file.


This routine writes the initialization data that Lotus requires
at the start of every worksheet. See LotusFileOpen for more
information.

You must pass the file number (handle) of a currently open file,
and the file must have been opened in BINARY mode. The handle
you pass to this routine is kept for internal use by the other
123-Write routines that write to the spreadsheet file. Do not
write anything to the file before calling this routine. Of
course you can if you want, but this routine will promptly over-
write it for you.

Again, don't close the file yourself, outside of 123-Write, use
the LotusClose routine instead.


EXAMPLE: OPEN "D:\LOTUS\TESTFILE.WK1" FOR BINARY AS #2
DOS 2.0+ CALL LotusHeader(2)

EXAMPLE: (For a network environment with SHARE.EXE installed.)

OPEN "F:\LOTUS\TESTFILE.WK1" FOR BINARY ACCESS WRITE AS #2
CALL LotusHeader(2)





22




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusLeft

SYNTAX: LotusLeft(count%)

count% is the number of columns.


LotusLeft is the equivalent of hitting the left arrow key in
Lotus. The next col to be written will be decreased by the
number of columns the routine is called with. Attempts to cursor
left beyond column 0 will leave the cursor in column 0 (A).


EXAMPLE: move% = 5
CALL LotusLeft(move%)
'The next cell to be written will be 5 to the
'left of the current cell, in the same row.



































23




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusLineFeed

SYNTAX: LotusLineFeed

When called, resets the internal column pointer to 0, (Column A)
and increases the row pointer by one. The next cell to be writ-
ten will one row lower in column A.


See LotusColFunction for an example of using LotusLineFeed.










































24




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought

NAME: LotusMCellMath

SYNTAX: LotusMCellMath(integerArray%())

integerArray%() is a single dimension array with
column and row coordinates and
the operation to be performed.

This routine is going to take some getting used to. It's purpose
is to write calculations to the current cell using multiple cell
references. (There is a limit to the number of referenced cells,
roughly 341(?) cells but I wouldn't want to try and find the
maximum.)

The integers in the array are read and acted upon sequentially.
Each cell operation requires three elements, the column number,
the row number and what to do with it, the operation code. The
routine starts reading from the lowest element in the array, so
be sure to put the first column reference in element 0 if you
don't use an OPTION BASE 1 statement or a DIM array% (1 to 12)
type statement. The operation codes, the third element of each
set, are:

0. end of calculation.
1. add next cell.
2. subtract next cell.
3. multiply by the next cell.
4. divide by the next cell.

Again, an example might be the best way to illustrate. A work-
sheet with 4 columns (0-3, A-D) of data. We want to take column
A's (0) value, add column B's (1) value to it, and subtract the
value in column D (3), placing the result in column E (4) for
each row.


See the next page for a code example.
















25




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


LotusMCellMath example.

DIM intArray%(1 TO 9)
.
worksheet file is opened etc...
.
startRow% = 3 'The operations and columns
endRow% = 30 'are constant, so set them:
intArray%(1) = 0 'Element '1' col number (A)
intArray%(3) = 1 'Element '3' operation: Add next
intArray%(4) = 1 'Element '1' col number (B)
intArray%(6) = 2 'Element '3' operation: Sub next
intArray%(7) = 3 'Element '1' col number (D)
intArray%(9) = 0 'Element '3' operation: End calc

FOR i% = startRow% TO endRow%
.
.
write the row data,
columns 0 to 3 (A - D)
.
.
intArray%(2) = i% 'The row numbers are going
intArray%(5) = i% 'to change to reflect the
intArray%(8) = i% 'row we're in.
'
'Then write the formula to column E:
'
CALL LotusMCellMath(intArray%())
CALL LotusLineFeed
NEXT i%





















26




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusPrintBorders

SYNTAX LotusPrintBorders(rulc%, rulr%, rlrc%, rlrr%,
culc%, culr%, clrc%, clrr%)


rulc% is the Border Row upper left column

rulr% is the Border Row upper left row

rlrc% is the Border Row lower right column

rlrr% is the Border Row lower right row

culc% is the Border Column upper left column

culr% is the Border Column upper left row

clrc% is the Border Column lower right column

clrr% is the Border Column lower right row

Yes, eight parameters for one call. Lotus 1-2-3 reads all
eight as one record, consequently it has to be written to the
file as one record. Now a key point. Lotus uses a unique method
to indicate an undefined range which is also used internally by
123-Write. It would be cumbersome to have to call this routine
with the proper values, so I selected either all column range
values equal to zero or all row range values equal to zero to
indicate an undefined range. When you define a column or row
print border in Lotus, it is acceptable to define one cell as the
'column' or one cell as the 'row', you can do that in 123-Write
also, with the exception of 'A1' as a border. To define row 1 as
the row border would require all the row values to be zero, same
thing for column A. Both of these may be common settings, but
123-Write will interpret them to be indicators of an undefined
border. When defining column A as the column border, from row
1, use: "0, 0, 0, x" where x is the bottom-most row of the print
range. To define row 1 as the row border, from column A, use:
"0, 0, x, 0" where x is the right-most column in the print range.

EXAMPLE:
'Set only border rows of rows 1 thru 4:
CALL LotusPrintBorders (0, 0, 0, 3, 0, 0, 0, 0)
'Set only border columns of columns A and B:
CALL LotusPrintBorders (0, 0, 0, 0, 0, 0, 1, 0)
'Set both the above as the borders:
CALL LotusPrintBorders (0, 0, 0, 3, 0, 0, 1, 0)




27




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusPrintMargins

SYNTAX: LotusPrintMargins(lm%, rm%, tm%, bm%, pageLen%)

lm% is the left margin

rm% is the right margin

tm% is the top margin

bm% is the bottom margin

pageLen% is the page length

LotusPrintMargins set the printer margins used by Lotus for
subsequent printing. When 123-Write opens (rather than appends
to) a worksheet file, these variables are set to the default
values of:
Left = 4
Right = 76
Top = 2
Bottom = 2
Page = 66

If needed, the default, or previously set (if the file was
opened for append) margins can be changed by calling this rou-
tine. NOTE that all the variables will be written to the work-
sheet file. If you want to change just the right margin for
example, you must still supply all the other parameters.


EXAMPLE:


lm% = 10
rm% = 130
tm% = 2
bm% = 2
page% = 66
CALL LotusPrintMargins(lm%, rm%, tm%, bm%, page%)

'In effect just changes the left and right print margins.











28




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusPrintRange

SYNTAX: LotusPrintRange (ulc%, ulr%, lrc%, lrr%)


ulc% is the upper left column of the range.

ulr% is the upper left row of the range.

lrc% is the lower right column of the range.

lrr% is the lower right row of the range.


LotusPrintRange sets up the default print range for the
worksheet. This allows you to predetermine the range, and then
you can just add a macro to print it when Alt-P is pressed.
Saves time in macro writing if the range is preset, by eliminat-
ing all the range input needed to set up the print range. The
example
below shows how it can be done. If you use the LotusGetLoc
routine and save the bottom-most row and right-most col to varia-
bles, then set the print range, it allows you to always define
the full range of cells for printing regardless of the size of
the worksheet.


EXAMPLE:

'Let's assume all the data has been written
'to the spreadsheet, and that the upper left
'corner of the print range is 0, 0 (A1) and the
'lower right corner is in the variables lrc% and
'lrr%

CALL LotusPrintRange (0, 0, lrc%, lrr%)
column% = LotusCol ("Z")
CALL LotusSetLoc (column%, 0) 'Macro in Z1
CALL LotusRange("\P", column%, 0, column%, 0) 'Name it
CALL LotusWriteStr("/PPAGQ") '1 cell macro

'The Alt-P macro is just a PRINT, PRINTER, ALIGN,
' GO, QUIT sequence.









29




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusPrintSetup

SYNTAX: LotusPrintSetup (setup$)

setup$ is the Lotus style print control string.


Assigns a printer setup string to the worksheet. The string
you pass to this routine is used exactly as received. An excep-
tion is that only the first 40 characters are used if the string
is over 40 characters long. Remember to use the back-slash (\)
character to start and three digit ASCII numbers to define the
setup string.


EXAMPLE:

'Setting my printer into near letter quality mode
'requires an ESC-W-1 string. ASCII 27, 88, 49

setup$ = "\027\088\049"
CALL LotusPrintSetup (setup$)






























30




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusPrintTitle

SYNTAX: LotusPrintTitle (title$, header%)


title$ is the string to assign to the print output.

header% is a flag to indicate a title (non-zero)
or a footer (zero)


When called, sets up either the print title, if header% is
non zero, or the print footer if header% is zero. The string
passed to this routine is truncated if over 240 characters.


The special formatting characters should be included in the
string passed to this routine. The | character can be used to
break the line into three separate text blocks, each aligned on
the page differently. The @ character will be replaced by the
current system time when the print command is issued. The third
format character is # which indicates page numbering. The page
number is only reset to 1 when the /Print command is invoked.


EXAMPLE:

header$ = "|Income Statement"
footer$ = "|2nd Quarter 1989"
CALL LotusPrintTitle (header$, -1)
CALL LotusPrintTitle (footer$, 0)

'Both the header and footer above will be centered,
'There is no 'Left' block.

header$="Income Statement||Page #"

'Will result in a left aligned title, with the page
'number right aligned for instance.













31




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusRange

SYNTAX: LotusRange(rangeName$, ulc%, ulr%, lrc%, lrr%)

rangeName$ is the name to assign.

ulc% is the upper left column of the range.

ulr% is the upper left row of the range.

lrc% is the lower right column of the range.

lrr% is the lower right row of the range.


LotusRange assigns a name of up to 15 characters to the
specified range. Any characters over 15 are ignored. The range
name supplied is parsed to replace any spaces with underscores,
and any non-alphanumeric characters are stripped, with the excep-
tion of the "\" character, so a macro name can be assigned. The
name passed to the routine remains unaltered.


EXAMPLE:

CALL LotusRange("Income", LotusCol("B"), 0, LotusCol("D"), 19)

Assigns the name INCOME to the range from B1 through D20.
























32




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusRight

SYNTAX: LotusRight(count%)

count% is the number of columns.


LotusRight is the equivalent of hitting the right arrow key in
Lotus. The next column to be written will be increased by the
number of columns the routine is called with. The maximum column
number is 255 (IV). Attempts to 'cursor' right beyond column 255
will leave the cursor in column 255.


EXAMPLE: move% = 5
CALL LotusRight(move%)
'The next cell to be written will be 5 columns
'to the right of the current column, in the
'same row.

































33




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusRowFunction

SYNTAX: LotusRowFunction(startCol%, endCol%, oper%)

startCol% represents the first col to operate on.

endCol% represents the last col to operate on.

oper% is set to one of the following:

1. @SUM the sum of the cells.
2. @AVG the average of the cells.
3. @COUNT the count of cells with
valid data.
4. @MIN the lowest value in the range.
5. @MAX the highest value in the range.

LotusRowFunction is a quick method of writing common row func-
tions to a cell, or a series of cells. The routine assumes the
row to operate on is the current row, so all that's needed in the
call is the first column to include and the last column to in-
clude.

To illustrate, lets say you're writing a spreadsheet where you
want the sum (@SUM) of the first four columns (A-D) in column E
of each row:

FOR i% = 1 TO LOF(dataFile%) \ LEN(dataRecord%)
.
...writing a row loop...
.
CALL LotusRowFunction(0, 3, 1) '@SUM the row.
CALL LotusLineFeed 'Get set for next row.
NEXT i%


















34




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusSetCellFormat

SYNTAX: LotusSetCellFormat(format$)

format$ is a 2 or 3 byte string where the first
letter indicates the type of display, and
the second (and third if needed) the number
of decimal places.

Sets the default cell display for subsequent cells. Default
string alignment can be changed, see LotusWriteStr.

Valid first characters are:

G General format (the default Lotus display).

F Fixed number of decimal places (no commas).

C Currency, dollar signs ($) and commas inserted.

P Percent, the value of the cell is multiplied by
100 by Lotus and displayed with a trailing
percent sign.

S Scientific format. Enough said.

, Commas inserted by thousands.

D Date format.

For the Fixed, Scientific, Currency, Percent, and , (comma)
formats, the next character(s) represents the number of
decimal places to display, from 0 through 15.

For the Date format, follow the "D" by the date format
number desired:
1 dd-mmm-yy "16-Feb-53"
2 dd-mmm "16-Feb"
3 mmm-yy "Feb-53"

any other value results in date format 1. These
formats are in keeping with the 1-2-3 ver 1a formats.

When the worksheet file is opened, the format string is set to
"F2", no commas and fixed at 2 decimal places.

EXAMPLES: CALL LotusSetCellFormat("F3") 'result: ####.###
CALL LotusSetCellFormat(",2") 'result: #,###.##
CALL LotusSetCellFormat("C2") 'result: $#,###.##



35




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusSetCol

SYNTAX: LotusSetCol(col%, colWidth%)


col% is the column number to set,

colWidth% is the width for the column.

Sets the column width for an individual column. See LotusSetCols
if there are many columns to set.

The Lotus default column width is 9 characters wide. You can set
the width of any column to another value by calling this routine.
The valid widths are 1 to 240 maximum.


EXAMPLE: CALL LotusSetCol(3, 20)
CALL LotusSetCol(LotusCol("D",20)

both set column D's width to 20 characters.































36




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusSetCols

SYNTAX: LotusSetCols(colWidths%())

colWidths%() is a single dimension array, the element
number indicates the column, the contents
of the element indicates the width for
the column.


LotusSetCols is for setting multiple columns to different widths
with one call. Whether you use this routine or set the column
widths individually with LotusSetCol is your call.

The integer array holds the width for each column. The position
in the array determines the column. If you want to use this
routine to set column zero, there must be a zero element in the
array passed. No OPTION BASE 1 statement in this case, or use a
DIM array%(0 to whatever%) statement to over-ride the OPTION BASE
statement.

With QuickBASIC 4.0+ and BASCOM 6.0+ it is possible to set lower
array bounds of other then 0 or 1, which is handy with this
routine. If you want to set columns 9 through 17 for example,
you can:

REDIM temp%(9 TO 17)
...set elements 9 through 17 to desired widths...
CALL LotusSetCols(temp%())

A lower bound less then 0 will result in NO column width data at
all being written.

If the width for a column (element) is less than 1 or greater
than 240 that column is skipped, and it's width is left at the
default setting of 9.

EXAMPLE: See above.














37




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusSetDateForm

SYNTAX: LotusSetDateForm (format%)

format% is the desired format number:

1 dd-mmm-yy 16-Feb-53
2 dd-mmm 16-Feb
3 mmm-yy Feb-53
(1-2-3 ver 2.0+ only) 4 mm-dd-yy 02-16-53
(1-2-3 ver 2.0+ only) 5 mm-dd 02-16

Setting the date format is optional, it will default to
format 1, the dd-mmm-yy arrangement when the worksheet is opened.
The date format you set here will be used with subsequent calls
to LotusDate.

Note that this is a change from version 1.0, where the date
format on calls to LotusDate was always format 1. All five
formats are available in Lotus 1-2-3 version 2.0 and greater.
Only formats 1, 2 and 3 are recognized by version 1a of Lotus.

A date format can be specified by calling LotusSetCellFormat
also. The problem with that method is that LotusSetCellFormat is
a 'Global' type setting, and will effect all cells written after
the call. This routine allows you to cut your code down by
setting both a date format and a standard cell format and not
being forced into the dd-mmm-yy setting.

EXAMPLE:

'Assume you're reading and writing 10 field records where
'the 5th and 7th fields are dates (all others numeric):

'In version 1.0 of 123-Write, code 'In version 1.1:
'is somewhat cumbersome:
CALL LotusSetDateForm("D4")
FOR i = 1 TO 10 FOR i = 1 TO 10
INPUT #inputFile, rec$ INPUT #inputFile, rec$
IF i = 5 OR i = 7 THEN IF i = 5 OR i = 7 THEN
CALL LotusSetCellFormat("D4") CALL LotusDate(rec$)
CALL LotusDate(rec$) ELSE
CALL LotusSetCellFormat("F2") a# = VAL(rec$)
ELSE CALL LotusWriteNum(a#)
a# = VAL(rec$) END IF
CALL LotusWriteNum(a#) NEXT
END IF
NEXT




38




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusSetLoc

SYNTAX: LotusSetLoc(newCol%, newRow%)


newCol% is the next column you want written,

newRow% is the next row to write.

Use LotusSetLoc to position the internal column and row pointers
to any cell (within 255 columns and 2048 rows) that you want to
write to next. Only thing to mention (again?) is that the column
and row coordinates are 0 based, but at least with the LotusCol
function, we can get a little help:


Example:

CALL LotusSetLoc(LotusCol("C"), 6)

Will result in the cell pointer being set to write to 'C7' next.































39




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought



NAME: LotusSetProtect

SYNTAX: LotusSetProtect (switch%)

switch% is 0 to turn off protection of subsequent
cells, non zero to turn on protection.


When the worksheet file is opened, either by LotusFileOpen
or LotusFileAppend, the default for every cell written to the
file is protected mode. Do not confuse the individual cell
protection generated by this sub program with the global work-
sheet protection set in LotusGlobal. Even though every cell is
marked as a 'protected' cell, it is not actually protected unless
the Worksheet/Global/Protection/Enable command is issued in
Lotus, or LotusGlobal (5, -1) is called. Once worksheet wide
protection is enabled as above, only cells in unprotected mode
can be written to from within Lotus, without going through the
command sequence above to Disable it.

If you are going to enable (turn-on) global worksheet pro-
tection, and you wish to have cells within the worksheet open for
change, then call this routine with the switch set to zero.

Even if you don't want to bother with the protection, but do
want to highlight some cells, then turn the protection off for
those cells. Lotus displays unprotected cells in a different
color on a color monitor, or in higher contrast on a monochrome
monitor.

All cells written after a call to this routine will be
either protected or unprotected, depending on the switch.

EXAMPLE:

'Highlight two cells, to show the date the worksheet was
'generated:

CALL LotusSetProtect(0) 'Cell protect off
CALL LotusWriteStr(CHR$(34)+"DATE:")
CALL LotusDate(DATE$)
CALL LotusSetProtect(-1) 'Restore default









40




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusSetStrFormat

SYNTAX: LotusSetStrFormat(format$)

format$ is any one of the following:

" Right justify text.
' Left justify text. (Default)
\ Repeating character.
^ Center text in the cell.

LotusSetStrFormat sets the DEFAULT label alignment character for
all strings written to the file (after this routine is called),
if you don't specify a prefix character in the string. See the
LotusWriteStr routine for more information on writing string
data.

If the format character you pass to this routine is not one of
the four available characters, the single quote (') is selected.
The single quote (left justify) is the default label prefix
character selected when the spreadsheet file is opened via Lotus-
FIleOpen.






























41




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusSetTimeForm

SYNTAX: LotusSetTimeForm (format%)

format% is the desired format number:

(am/pm indicator) 1 hh:mm:ss(am/pm) 09:33:45pm
(am/pm indicator) 2 hh:mm(am/pm) 09:33pm
(24 hour clock) 3 hh:mm:ss 21:33:45
(24 hour clock) 4 hh:mm 21:33

Setting the time format is optional, it will default to
format 1, the hh:mm:ss(am/pm) arrangement when the worksheet is
opened. The time format you set here will be used with subse-
quent calls to LotusTime.

Note that this is a change from version 1.0, where the time
format on calls to LotusTime was always format 1. All four
formats are available only in Lotus 1-2-3 version 2.0 and great-
er.

A time format can be specified by calling LotusSetCellFormat
also. The problem with that method is that LotusSetCellFormat is
a 'Global' type setting, and will effect all cells written after
the call. This routine allows you to cut your code down by
setting both a time format and a standard cell format and not
being forced into the dd-mmm-yy setting.



EXAMPLE:

An example here would look the same as the two given with
the LotusSetDateForm routine, and since they're both so similar,
I'll refer you to that routine for an example.

















42




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusSortKey

SYNTAX: LotusSortKey (primry%, ulc%, ulr%, lrc%, lrr%, ascnd%)

primry% indicates whether to set the primary sort key
if non-zero or the secondary sort key if zero

ulc% is the upper left column of the key range.

ulr% is the upper left row of the key range.

lrc% is the lower right column of the key range.

lrr% is the lower right row of the key range.

ascnd% indicates the sort order, ascending if non-
zero, descending if zero.

If a sort range has been set, the primary sort key and the
secondary sort key can be set via this routine.

As in LotusPrintRange, this routine can alleviate the need
for long macros. If the range of data to manipulate is known, it
can all be set up via 123-Write, and a much simpler macro can be
written, one that doesn't need to determine boundaries etc.

Note that the key ranges must be within the data sort range.


EXAMPLE:

CALL LotusSortKey (-1, 0, 0, 0, endRow%, -1)

'Sets the primary sort key to from cell A1 to cell Ax
'where x is a variable row number. Like the last row
'written.
















43




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusSortRange

SYNTAX: LotusSortRange(ulc%, ulr%, lrc%, lrr%)

ulc% is the upper left column of the sort range.

ulr% is the upper left row of the sort range.

lrc% is the lower right column of the sort range.

lrr% is the lower right row of the sort range.


LotusSortRange sets up the default sort range for the work-
sheet. This allows you to predetermine the range, and then you
can just add a macro to print it when Alt-S is pressed. Saves
time in macro writing if the range is preset, by eliminating all
the range input needed to set up the print range. The example
below shows how it can be done. If you use the LotusGetLoc
routine and save the bottom-most row and right-most column to
variables, then set the sort range, it allows you to always
define the full range of cells for sorting regardless of the size
of the worksheet.


EXAMPLE:

'Let's assume all the data has been written
'to the spreadsheet, and that the upper left
'corner of the sort range is 0, 0 (A1) and the
'lower right corner is in the variables lrc% and
'lrr%

CALL LotusSortRange (0, 0, lrc%, lrr%)
column% = LotusCol ("AA")
CALL LotusSetLoc (column%, 0) 'Macro in AA1
CALL LotusRange("\S", column%, 0, column%, 0) 'Name it
CALL LotusWriteStr("/DSGQ") '1 cell macro

'The Alt-S macro is just a DATA, SORT, GO, QUIT sequence.












44




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusTime

SYNTAX: LotusTime(timeStr$)

timeStr$ is a valid string representation of a
legitimate time. (What?)

The expected format is: hh:mm:ss or hh:mm

Don't really know how much use there may be for this routine, but
since I included the numeric date handling capability, I figured
I should also include time handling.

Limited fiddling is done with the string you pass to this rou-
tine. If it can't make sense out of the string, it writes the
string to the file instead of the numeric value of the time. The
routine is coded to use the format set with LotusSetTimeForm,
and if there isn't a format set, it uses time format 1, Lotus
version 2.0+ will display it in:

hh:mm:ss AM
or
hh:mm:ss PM

>> NOTE <<
Time values were not introduced in Lotus until release 2.0.
You may use this routine to write a worksheet that will be read
by a ver 1a copy of Lotus however. The values will be correct,
but it is impossible to display them in "time format" with ver-
sion 1a. The cell format indicator in 1a will display (?) in-
stead of (D6) as it does in 2.0+. The cell contents are a frac-
tion, indicating the fraction of a day that has passed.

In 1a you can convert the fractional value to it's respective
hours, minutes, seconds by using the following values:
1 hour equals 0.0416666
1 minute equals 0.0006944
1 second equals 0.0000115

So, 01:30:30 PM would be (13:30:30) around .562847222
.562847222 divided by 0.0416666 equals: 13.50835494 or 13:
- .541665800 (13 times 0.0416666) leaves
.021181422 divided by 0.0006944 equals: 30.50319988 or :30

enough said....(So I like fooling with numbers...)







45




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusToday

SYNTAX: LotusToday


Writes the Lotus function @TODAY to the current cell. The cell
is formatted for dd-mmm-yy display (D1) if there is no default
date format set. The system date will be displayed in the target
cell when the worksheet is loaded into Lotus.











































46




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusUp

SYNTAX: LotusUp(count%)

count% is the number of rows.


LotusUp is the equivalent of hitting the up arrow key in Lotus.
The next row to be written will be decreased by the number of
rows the routine is called with. Attempts to 'cursor' above
beyond row 0 will leave the cursor in row 0.


EXAMPLE: move% = 5
CALL LotusUp(move%)
'The next cell to be written will be 5 rows
'above the current row, in the same column.



































47




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusWriteErr

SYNTAX: LotusWriteErr


This routine is used internally to write the function @ERR to the
current cell when you do something out of line, like pass parame-
ters that are outside of the allowed range. Seems only fair that
you should be able to call it when an end user of your program
does something out of line.

I know, end users always respond to input prompts in only the
ways we programmers expect, but it's here just in case.







































48




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusWriteInt
NAME: LotusWriteNum

SYNTAX: LotusWriteInt(integerVal%)
SYNTAX: LotusWriteNum(doubleVal#)


OK, so the meat and potatoes come near the end of the documenta-
tion. It's alphabetical.


Both these routines write a value to the current cell. There
probably isn't a need for two routines to write a numeric value
to a cell. The only difference is the resultant length of the
data being written. If your application is reading from a random
access file, where you know the data value is sure to be a two
byte signed integer, use LotusWriteInt for some space saving.

Any applications that need the full range of numbers should call
the LotusWriteNum routine instead, to maintain decimal values.
Note that this routine requires an eight byte double precision
value though, not a 4 byte single precision value.






























49




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


NAME: LotusWriteStr

SYNTAX: LotusWriteStr(dat$)

dat$ string data to be written.

Writes a label (string data) to the target cell.

One item of note is that the currently set label prefix
character, (single quote upon startup, or what you define as
default with LotusSetStrFormat) is used if the data string sent
to this routine does not include a label prefix character as it's
first byte. In other words, if you call this routine with a data
string that has a valid label prefix character in the leftmost
position, that prefix character will take preference.

This routine will not actually write a null ("") cell, a cell of
space characters or a cell of CHR$(0)'s. If called with any of
those parameters, it will advance the internal column pointer to
the next column.

EXAMPLE:

CALL LotusFileOpen("D:\LOTUS\TESTFILE")

'default label prefix=" ' " left alignment.
'we're at cell A1, write a title, will be left aligned.

CALL LotusWriteStr("Test worksheet written by 123-Write")
CALL LotusLineFeed

'Then write the column headings in row 1...

CALL LotusWriteStr("^Name") 'Centered
CALL LotusWriteStr("^Address") 'Centered
CALL LotusWriteStr(CHR$(34) + "Balance") 'Right justify
CALL LotusLineFeed

'Next the ever popular "=======" repeating character.

FOR i = 1 TO 3
CALL LotusWriteStr("\=")
NEXT

CALL LotusLineFeed







50




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought

ERRORS.....From Lotus 1-2-3

There really shouldn't be any when you retrieve a worksheet in
Lotus, if there is advise me as soon as possible, and include a
printout of the code if possible. See below on contacting me.

There is a series of real nasty error messages that I encountered
developing these routines. Lotus beeps, prints an error message
"Worksheet Revision out of date. Press HELP!" is one that comes
to mind, there are a number of others too! When you press F1,
the help key, a message comes up advising you to call Lotus
Customer Support right away. Don't panic, I know I never do. Go
over your code and be sure that everything's being called proper-
ly, then if need be, write me at the address on the registration
form.

123-Write ERRORS....

The only errors that will be returned by 123-Write are the
codes returned by LotusHeader. Anything else is blissfully
ignored.

BASIC ERRORS:

These are yours to trap or ignore or fix. I believe that if
you have error trapping enabled, errors that occur in the 123-
Write routines should cause that trap routine to be called.
Short of having a drive door open, I can't envision any way a
user can mess up while running these routines. (Where have I
heard those famous last words before?)

CONTACTING the author:

Via Modem:

Questions can be answered via a call to The Bounty, 201-431-4088
(N81, 9600/2400/1200) the support BBS. Brent Yandell, the Sysop
was kind enough to offer his board for support question and
answers. Address your message to: TOM VOUGHT

Otherwise:

Write me at: 5 John Street
Morganville, NJ 07751









51




123-Write ver 1.1
Copyright (c) 1989, Thomas J. Vought


FINIS:

I hope that these routines make your coding easier, and at
the same time give you the tools to improve your programs. If
you enjoy them, let me know, if there's something you don't like,
think should be improved, or want to see included etc, let me
know. Write my date of birth on the registration form if you get
a chance. Anyway, enjoy!

<< Tom >>










































52




Routine Quick Reference and Index

LotusCellFunction(col%, row%, funcNumb%), 9
LotusCellMath(col1%, row1%, col2%, row2%, oper%) 10
LotusClose 11
LotusCol (column$) 12
LotusColFunction (startRow%, endRow%, oper%) 13
LotusConstMath(col%, row%, constVal#, oper%) 14
LotusDate(dat$) 15
LotusDown(count%) 16
LotusFileAppend(fileName$, ecode%) 17
LotusFileOpen(fileName$) 19
LotusGetLoc(whatCol%, whatRow%) 20
LotusGlobal(item%, switch%) 21
LotusHeader(fileHandle%) 22
LotusLeft(count%) 23
LotusLineFeed 24
LotusMCellMath(integerArray%()) 25
LotusPrintBorders(rulc%, rulr%, rlrc%, rlrr%,
culc%, culr%, clrc%, clrr%) 27
LotusPrintMargins(lm%, rm%, tm%, bm%, pageLen%) 28
LotusPrintRange (ulc%, ulr%, lrc%, lrr%) 29
LotusPrintSetup (setup$) 30
LotusPrintTitle (title$, header%) 31
LotusRange(rangeName$, ulc%, ulr%, lrc%, lrr%) 32
LotusRight(count%) 33
LotusRowFunction(startCol%, endCol%, oper%) 34
LotusSetCellFormat(format$) 35
LotusSetCol(col%, colWidth%) 36
LotusSetCols(colWidths%()) 37
LotusSetDateForm (format%) 38
LotusSetLoc(newCol%, newRow%) 39
LotusSetProtect (switch%) 40
LotusSetStrFormat(format$) 41
LotusSetTimeForm (format%) 42
LotusSortKey (primry%, ulc%, ulr%, lrc%, lrr%, ascend%) 43
LotusSortRange(ulc%, ulr%, lrc%, lrr%) 44
LotusTime(timeStr$) 45
LotusToday 46
LotusUp(count%) 47
LotusWriteErr 48
LotusWriteInt(integerVal%) 49
LotusWriteNum(integerVal%) 49
LotusWriteStr(dat$) 50













53