SaveGridToWorkbook automatically create formula from cell value

[DougHennig]

I added some new functionality to SaveGridToWorkbook if you're interested. If the value of a cell in a grid starts with '=', treat it like a formula. Here's the code:

*-*		Add the cell data values and cell formatting
		SELECT &lcGridAlias
		SCAN
			lnRow = lnRow + 1
			RAISEEVENT(this, "OnShowStatusMessage", 3, lnRow)
			FOR lnCol=1 TO lnColCount
				loColumn = toGrid.Columns(laColOrder[lnCol, 2])
				lcField  = loColumn.ControlSource
				lxCellValue = EVALUATE(lcField)
				IF loGrid.Columns(laColOrder[lnCol, 2]).FieldPercent
					lxCellValue = lxCellValue / 100
				ENDIF
*** DH 2024-02-23: treat a value starting with "=" as a formula
*				this.SetCellValue(lnWB, lnSh, lnRow, lnCol+tnBegCol-1, lxCellValue)
				if vartype(lxCellValue) = 'C' and left(lxCellValue, 1) = '='
					This.SetCellFormula(lnWB, lnSh, lnRow, lnCol+tnBegCol-1, strtran(lxCellValue, '{ROW}', transform(lnRow), -1, -1, 1))
				else
					this.SetCellValue(lnWB, lnSh, lnRow, lnCol+tnBegCol-1, lxCellValue)
				endif vartype(lxCellValue) = 'C' ...
*** DH 2024-02-23: end of change
				IF loGrid.Columns(laColOrder[lnCol, 2]).DynamicProperties

[ggreen86]

I will add this feature into the next release.

I am also considering how I might add formula support to SaveGridToWorkbook and SaveGridToWorkbookEx methods by adding a custom property to the grid column object. I use a custom class for the columns which is then assigned to the grid (also a custom class) via the MemberClass property. So, it is easy for me to define the property. It could also be added at run-time if not using a custom column class. I am thinking to name the property 'ColumnFormula' and the developer would put the desired formula; i.e., SUM, AVG, etc., to be added after the last row is outputted as the property value. What are your thoughts on this approach?

Greg

[DougHennig]

Sounds like a good idea.