ActivateWS

Procedure ActivateWS(theWkshtName:STRING);
VAR
	SSHd:HANDLE;
BEGIN
	SSHd:=GetObject(theWkshtName);
	SelectSS(SSHd);
END;

What it does
Selects the worksheet as specified by the string parameter.

Function WSExistH(WSName:STRING;VAR WSHd:HANDLE):BOOLEAN;
BEGIN
	WSHd:=GetObject(WSName);
	IF WSHd <> NIL THEN
		WSExist:=True
	ELSE
		WSExist:=False;
END;

What it does
If the specified worksheet exists, the function returns TRUE, and a handle to the worksheet; otherwise the function returns FALSE.

Function WSExist(WSName:STRING):BOOLEAN;
VAR
	WSHd:HANDLE;
BEGIN
	WSHd:=GetObject(WSName);
	IF WSHd <> NIL THEN
		WSExist:=True
	ELSE
		WSExist:=False;
END;

What it does
If the specified worksheet exists, the function returns TRUE. If there is no worksheet, the function returns FALSE.

Procedure BuildWorksheetDB(WSName,RecordName:STRING;StartRow:INTEGER);
VAR
	Q,CriteriaString:STRING;

Procedure ActivateWS(theWkshtName:STRING);
VAR
	SSHd:HANDLE;
BEGIN
	SSHd:=GetObject(theWkshtName);
	SelectSS(SSHd);
END;


BEGIN
	Q:=Chr(39);
	
	CriteriaString:=Concat('=Database((R IN[',Q,RecordName,Q,']))');
	ActivateWS(WSName);
	
	LoadCell(StartRow,0,CriteriaString);
END;

What it does
Creates data base subrows in a MiniCad worksheet to display record-field or other information from objects. You supply the worksheet name, record name, and start row for database information to be displayed.

Procedure BuildDBColumnRF(WSName,Record,Field:STRING;Row,Column:INTEGER);
VAR
	theCriteria:STRING;


Procedure ActivateWS(theWkshtName:STRING);
VAR
	SSHd:HANDLE;
BEGIN
	SSHd:=GetObject(theWkshtName);
	SelectSS(SSHd);
END;

Function BuildRecCrit(Record,Field:STRING;RecordOnly,UseEqual:BOOLEAN):STRING;
VAR
	Q,CriteriaString:STRING;
BEGIN
	Q:=Chr(39);
	
	IF RecordOnly THEN BEGIN
		CriteriaString:=Concat('(R IN[',Q,Record,Q,'])');
		IF UseEqual THEN
			CriteriaString:=Concat('=',CriteriaString);
	END
	ELSE BEGIN
		CriteriaString:=Concat('(',Q,Record,Q,'.',Q,Field,Q,')');
		IF UseEqual THEN
			CriteriaString:=Concat('=',CriteriaString);
	END;
	
	BuildRecCrit:=CriteriaString;
END;

BEGIN
	ActivateWS(WSName);
	LoadCell(Row,Column,BuildRecCrit(Record,Field,FALSE,TRUE));
END;

What it does
Loads a user specified record and field to be displayed in a worksheet database subrow. You supply the name of the worksheet,the record and the field to be displayed, and the row and column where the criteria will be loaded.

Subroutine Notes
BuildDBColumnRF makes use of other subroutines to construct the criteria. Since they are defined within the BuildDBColumnRF function, their scope is completely local to the function.

Procedure BuildDBColumn(WksheetName,CriteriaString:STRING;Row,Column:INTEGER);
VAR
	theCriteria:STRING;


Procedure ActivateWS(theWkshtName:STRING);
VAR
	SSHd:HANDLE;
BEGIN
	SSHd:=GetObject(theWkshtName);
	SelectSS(SSHd);
END;

BEGIN
	ActivateWS(WksheetName);
	LoadCell(Row,Column,CriteriaString);
END;

What it does
Loads a user specified criteria to be displayed in a worksheet database subrow. You supply the name of the worksheet,the criteria string, and the row and column where the criteria will be loaded.

Procedure Form2Number(WSName:STRING;StartRow,StartCol,EndRow,EndCol:INTEGER);
VAR
	theValue:REAL;
	SSHd:HANDLE;
	i,j:INTEGER;
BEGIN
	i:=StartCol;
	j:=StartRow;
	SSHd:=GetObject(WSName);
	SelectSS(SSHd);
	WHILE i <= EndCol DO BEGIN
		WHILE j<=EndRow DO BEGIN
			IF CellHasNum(SSHd,j,i) THEN BEGIN
				theValue:=GetCellNum(SSHd,j,i);
				LoadCell(j,i,Num2Str(3,theValue));
			END;
			j:=j+1;
		END;
		i:=i+1;
		j:=StartRow;
	END;
END;

What it does
Converts a specified range of cells in the worksheet from a formula format to the number returned by the formula. Useful for exporting data to other applications where MiniCad's functions(e.g.,Count,BotBound) are not supported.

Subroutine Notes
After conversion, use MiniCad's export options, or the function WKSExport, to export the data to your favorite worksheet.

Procedure WKSExport(WkshtName,TargetFile:STRING;StartRow,StartCol,EndRow,EndCol:INTEGER;Formulas:BOOLEAN);
VAR
	Rows,Cols,RCount,CCount:INTEGER;
	CellValue,CR:STRING;
	SSHd:HANDLE;
	Stop:BOOLEAN;

Function BoundsChk(Rows,Cols,RStart,REnd,CStart,CEnd:INTEGER):BOOLEAN;
VAR
	errorFound:BOOLEAN;
BEGIN
	IF (RStart <= 0) OR (RStart > Rows) OR (REnd > Rows) OR (RStart > REnd) OR
	(CStart <= 0) OR (CStart > Cols) OR (CEnd > Cols) OR (CStart > CEnd) THEN BEGIN
		errorFound:=True;
	END
	ELSE BEGIN
		errorFound:=False;
	END;
	
	BoundsChk:=errorFound;
END;

BEGIN
	RCount:=StartRow;
	CCount:=StartCol;
	CR:=Chr(13);
	
	SSHd:=GetObject(WkshtName);
	SprdSize(SSHd,Rows,Cols);
	
	Stop:=BoundsCheck(Rows,Cols,StartRow,EndRow,StartCol,EndCol);
	IF NOT Stop THEN BEGIN
		Rewrite(TargetFile);
		WHILE RCount <= EndRow DO BEGIN
			WHILE CCount <= EndCol DO BEGIN
				IF Formulas THEN BEGIN
					CellValue:=GetCellStr(SSHd,RCount,CCount);
				END
				ELSE BEGIN
					CellValue:=Num2Str(3,GetCellNum(SSHd,RCount,CCount));
				END;
				Write(CellValue);
				IF CCount < EndCol THEN BEGIN
					Tab(1);
				END
				ELSE BEGIN
					Write(CR);
				END;
				CCount:=CCount+1;
			END;
			RCount:=RCount+1;
			CCount:=StartCol;
		END;

		Close(TargetFile);
	END;
END;
Run(WKSExport);

What it does
Exports a selected range of cells to a tab delimited text file, which can be imported into spreadsheet or other programs. You specify the worksheet name, name of output file, range to export, and whether to export formulas or values.

Subroutine Notes
The subroutine BoundsChk ensures you are using a valid worksheet range.

Procedure VFind(WSName,MatchValue:STRING; LookupCol,RangeStart,RangeEnd:INTEGER;VAR hitLocation:INTEGER);
VAR
	totalRows,totalCols:INTEGER;
	SSHd:HANDLE;
	CellVal:STRING;
	Found,Stop:BOOLEAN;
	
Function BoundsChk(Rows,Cols,RStart,REnd,LCol:INTEGER):BOOLEAN;
VAR
	errorFound:BOOLEAN;
BEGIN
	IF (RStart <= 0) OR (RStart > Rows) OR (REnd > Rows) OR (RStart >= REnd) OR
	(LCol <= 0) OR (LCol>Cols) THEN BEGIN
		errorFound:=True;
	END
	ELSE BEGIN
		errorFound:=False;
	END;
	
	BoundsChk:=errorFound;
END;

BEGIN
	SSHd:=GetObject(WSName);
	SprdSize(SSHd,totalRows,totalCols);
	Stop:=BoundsChk(totalRows,totalCols,RangeStart,RangeEnd,LookupCol);
	
	IF NOT Stop THEN BEGIN
		REPEAT
			CellVal:=GetCellStr(SSHd,RangeStart,LookupCol);
			IF CellVal = MatchValue THEN BEGIN
				hitLocation:=RangeStart;
				Found:=True;
			END
			ELSE BEGIN
				RangeStart:=RangeStart+1;
				IF RangeStart > RangeEnd THEN BEGIN
					Found:=True;
					AlrtDialog('Error: Value not found');
				END;
			END;
		UNTIL Found OR (RangeStart > RangeEnd);
	END
	ELSE BEGIN
		AlrtDialog('Error : Bad range value');
	END;
	Found:=False;
END;

What it does
A vertical lookup search engine for use with the worksheet. VFind looks for a matching string, and returns the row location of the match. VFind can be used with ReturnHOffset to find and return data associated with the match value. You supply the worksheet name and match value; you can also preset or dynamically supply range values and offset for search and retrieval of data.

Subroutine Notes
1) The row location returned is an INTEGER.
2) VFind also performs bounds checking.

Procedure HOffsetReturn(WSName:STRING;baseRow,targetCol:INTEGER;retFormula:BOOLEAN;VAR targetValue:STRING);
VAR
	SSHd:HANDLE;
	hasFormula:BOOLEAN;
BEGIN
	SSHd:=GetObject(WSName);
	hasFormula:=CellHasNum(SSHd,baseRow,targetCol);
	
	IF hasFormula AND NOT retFormula THEN BEGIN
		targetValue:=Num2Str(2,GetCellNum(SSHd,baseRow,targetCol));
	END
	ELSE BEGIN
			targetValue:=GetCellStr(SSHd,baseRow,targetCol);
	END;
END;

What it does
Returns the value in the specified cell. HOffsetReturn is intended for use with VFind to retrieve data associated with the match value supplied to VFind. The procedure also allows you to specify whether a numeric value generated by a formula is returned, or to return the formula itself.

Procedure VSeek(WSName,MatchString:STRING; LookupCol,RangeStart,RangeEnd,TargetCol:INTEGER;VAR targetValue:STRING);
VAR
	totalRows,totalCols:INTEGER;
	SSHd:HANDLE;
	CellVal:STRING;
	Found,Stop:BOOLEAN;
	
Function BoundsChk(Rows,Cols,RStart,REnd,LCol,TCol:INTEGER):BOOLEAN;
VAR
	errorFound:BOOLEAN;
BEGIN
	IF (RStart <= 0) OR (RStart > Rows) OR (REnd > Rows) OR (RStart >= REnd) OR
	(LCol <= 0) OR (TCol <= 0) OR (LCol>Cols) OR (TCol>Cols) THEN BEGIN
		errorFound:=True;
	END
	ELSE BEGIN
		errorFound:=False;
	END;
	
	BoundsChk:=errorFound;
END;

BEGIN
	SSHd:=GetObject(WSName);
	SprdSize(SSHd,totalRows,totalCols);
	Stop:=BoundsChk(totalRows,totalCols,RangeStart,RangeEnd,LookupCol,TargetCol);
	
	IF NOT Stop THEN BEGIN
		REPEAT
			CellVal:=GetCellStr(SSHd,RangeStart,LookupCol);
			IF Pos(MatchStr,CellVal) <> 0 THEN BEGIN
				targetValue:=GetCellStr(SSHd,RangeStart,TargetCol);
				Found:=True;
			END
			ELSE BEGIN
				RangeStart:=RangeStart+1;
				IF RangeStart > RangeEnd THEN BEGIN
					Found:=True;
					AlrtDialog('Error: Value not found');
				END;
			END;
		UNTIL Found OR (RangeStart > RangeEnd);
	END
	ELSE BEGIN
		AlrtDialog('Error : Bad range value');
	END;
END;

What it does
Another vertical lookup search engine for use with the worksheet. This version also looks for a matching substring, rather than an exact match. You supply worksheet name and match value to be searched for, as well as the location(target column) of the information to be returned.

Subroutine Notes
1) Target data is returned as a STRING value.
2) Procedure also performs bounds checking.

[Home][Previous]