[Calc, Basic] Formatted form controls to sheet cells

[Villeroy]

Edit: 2009-06-08: Found some interesting new options with formatted fields setting FormulaLocal rather than the text value. See comments near the end of the code. Added 2 more examples to the attached document.

Edit: 2015-08-13: Added LibreOffice compatibility.

Certain form controls can be bound to spreadsheet cells since they produce either one of simple text or (decimal) number. Both simple data types are supported by sheet cells.
Date controls and time controls can not be bound to sheet cells. I think this is because both types are not directly supported by spreadsheets. Only databases have distinct field types for dates, times and time-stamps.
I have no idea why the currency fields (producing double floats) and formatted text fields are not supported.

The following macro can write the value of the above mentioned 4 control types into a certain cell. The cell is supposed to be named according to the respective control, so the macro writes the value of control "myDate" into a named cell "myDate".
The code is designed to work independent from the formatting of either the control or the cell. It just puts the right value into the right cell. Format the cell to your liking.

Code: Select all

REM  *****  BASIC  *****
Sub FormattedControlToCell(oEv)
REM Link named cells to values of date, time, currency and pattern controls.
REM Name the cells according to the respective control names.
REM Assign the "Text modified" event of the control to this macro.
REM The macro just puts the right value leaving all formatting to the user.
	obj = oEv.Source
'mymri obj
	sName = obj.Model.Name
	oName = ThisComponent.NamedRanges.getByName(sName)
	oCell = oName.getReferredCells()
	if HasUnoInterfaces(obj, "com.sun.star.awt.XDateField")then
		'OpenOffice getDate() returns ISO-date as integer YYYYMMDD, for instance: 19991231
		'LibreOffice getDate() returns an UNO struct
		vD = obj.getDate()
		if isUnoStruct(vD) then
			REM this is LibreOffice
			sVal = format(vD.Year,"0000") & format(vD.Month, "00") & format(vD.Day, "00")
		else 'OpenOffice
			sVal = Format(vD,"00000000")
		endif
		' we let the spreadsheet calculate the date from the split digits
		oCell.setFormula("=DATE("& left(sVal,4) &";"& mid(sVal,5,2) &";"& right(sVal,2) &")")
		oCell.setValue(oCell.getValue())
	elseif HasUnoInterfaces(obj, "com.sun.star.awt.XTimeField")then
		'OpenOffice getTime() returns time as integer HHMMSS00, for instance: 23304599
		'LibreOffice getTime() returns an UNO struct
		vT = obj.getTime()
		if isUnoStruct(vT) then
			REM this is LibreOffice
			sVal = format(vT.Hours,"00") & format(vT.Minutes, "00") & format(vT.Seconds, "00") & format(vT.NanoSeconds, "00")
		else 'OpenOffice
			sVal = Format(vT,"00000000")
		endif
		' spreadsheet times are fractions of a day
		hh = cDbl(left(sVal,2))/24 ' hours
		mm = cDbl(mid(sVal,3,2))/1440 'minutes
		ss = cDbl(right(sVal,4))/8640000 '1/100th sec.
		oCell.setValue(hh + mm + ss)
	elseif HasUnoInterfaces(obj, "com.sun.star.awt.XCurrencyField")then
		oCell.setValue(obj.getValue()) 'it's a double
	elseif HasUnoInterfaces(obj, "com.sun.star.awt.XPatternField")then
		REM FormulaLocal treats the string like input in the formula bar,
		REM including numeric evaluation and formula-input.
		oCell.FormulaLocal = obj.getString()
		REM Apply special number format "Text" to avoid evaluation or
		REM use this line instead of FormulaLocal:
		REM oCell.setString(obj.getString())
	endif
End Sub

[Tutorial] How to install a code snippet (or simply use the macro-organizer to move the module of the attached document)

[zaslaw]

I saved formatted_controls2.ods, but when I open it, enable macros, and try to change a date in the date form control (DateField), I get this error message:

A scripting error occurred while running the Basic script
vnd.sun.star.script:FormLib.Module1.formattedControlToCell?
language=Basic&location=document.

Message: The following Basic script could not be found:
library:'FormLib'
module:'Module1'
method:'FormattedControlToCell'
location:'document'

Any help is appreciated. Thank you.

[RPG]

Hello

I did test it. When I re-assign the macro to the control it was working.
Maybe there is changed something in assigning the macro to a control.
You must set the form/spreadsheet in edit mode for forms.

Romke

[Villeroy]

I moved the module to "Standard", reassigned the events and attached a third version.
edit: The library was not the problem. The events pointed to the wrong modules.

[Villeroy]

Please, learn programming! It is not a black magic like the things you do when you copy other people's code and edit it by mere intuition.
The macro is designed to work with any pair of a named cell and an equally named control.

obj = oEv.Source the form control which triggered the macro call
sName = obj.Model.Name is the name of the control model (what you assigned in the control's property dialog
oName = ThisComponent.NamedRanges.getByName(sName) the range name having the same name [Insert>Names>Define...]
oCell = oName.getReferredCells() the cells that are referenced by that named reference, assuming that it is a single cell.
if HasUnoInterfaces(obj, "com.sun.star.awt.XDateField")then ... tests if the calling form control is of a certain type. Using the macro with date controls only, you don't need to test at all.

On the rare occasions when I write macros I try to write them so you do not need to touch the code. Touch the objects using the code and name them according to the macro logic.

[zaslaw]

Thanks for the new module! I was able to use it and to create additional calendar controls and associated datafields by copying the original control and modifying the value of the Name field in the control properties dialog box, general tab, to a new cell identifier in the spreadsheet (for example, DataField1).

[zaslaw]

Villeroy -- I will add your very helpful explanation as comments to my copy of the code. Thank you.

You were probably responding to a post I deleted about the time you were making yours, I believe. I realized I had said something pretty stupid and went back and got it to work.

I just started using open office. I've been a programmer since 1961 when I started on an IBM 650, http://www-03.ibm.com/ibm/history/exhib ... 0_tr1.html, a machine with no assembler, programs coded directly in machine language. After that it was, over the years, Fortran II, Pascal, Lisp, OPS4, typesetting markup languages, C, Tcl/Tk, and others I can't recall. Yes, I was being lazy. I'm a volunteer for a non-profit now, and a bit slower off the mark. So I find your comment a bit harsh if perhaps deserved in this instance.

What does "mri" in the comment
'mymri obj
refer to?

[Villeroy]

http://extensions.services.openoffice.org/project/MRI tells you everything about any object.
Sorry, I thought you were one of those copy&paste programmers or another user refusing to use.