Page 1 of 1
[Calc, Basic] Formatted form controls to sheet cells
Posted: Thu May 14, 2009 12:09 pm
by 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
(or simply use the macro-organizer to move the module of the attached document)
Re: [Calc, Basic] Formatted form controls to sheet cells
Posted: Sun Dec 06, 2009 12:13 am
by 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.
Re: [Calc, Basic] Formatted form controls to sheet cells
Posted: Sun Dec 06, 2009 12:49 am
by 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
Re: [Calc, Basic] Formatted form controls to sheet cells
Posted: Sun Dec 06, 2009 10:39 am
by 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.
Re: [Calc, Basic] Formatted form controls to sheet cells
Posted: Sun Dec 06, 2009 12:12 pm
by 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.
Re: [Calc, Basic] Formatted form controls to sheet cells
Posted: Sun Dec 06, 2009 12:14 pm
by 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).
Re: [Calc, Basic] Formatted form controls to sheet cells
Posted: Sun Dec 06, 2009 8:38 pm
by 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?
Re: [Calc, Basic] Formatted form controls to sheet cells
Posted: Sun Dec 06, 2009 8:58 pm
by 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.