[Solved] Setting date from macro to cell

Discuss the spreadsheet application

[Solved] Setting date from macro to cell

Postby mr00v3ck » Fri Oct 11, 2013 10:24 am

Struggling here with a little,simple problem with dates. Got a dialogbox with a date field, where user inputs a future date. Need to set this date in a cell.

Tried these:
Code: Select all   Expand viewCollapse view
baza.GetCellByPosition(39,value).setString(oDialog_baza.getControl("DateField1").getText)
returns = 'XX.YY.ZZZZ, as text

baza.GetCellByPosition(39,value).setValue(oDialog_baza.getControl("DateField1").getDate)
returns = ZZZZYYXX

baza.GetCellByPosition(39,value).setValue(oDialog_baza.getControl("DateField1").getText)
returns = 0


What i need is to simply set date as... date. Not as string, nor value, but date (XX.YY.ZZZZ format).
setDate doesn't work.

Any ideas?
Last edited by mr00v3ck on Fri Oct 11, 2013 10:56 am, edited 1 time in total.
Win XP SP2 / OpenOffice 3.2.1
mr00v3ck
 
Posts: 11
Joined: Thu May 16, 2013 9:42 am

Re: Setting date from macro to cell

Postby Villeroy » Fri Oct 11, 2013 10:48 am

The easiest way to write a "date value" into a cell is:
Code: Select all   Expand viewCollapse view
oCell.FormulaLocal = "2013-10-11"

which is just a sophisticated way to put value 41558.

Any cell has a string, a value and a formula at the same time. The formula is translated to a local formula which is shown in the status bar. The ISO-date is the one and only local formula string that is recognized unambiguously and independent from local idieosynchracies. No matter which of the 150 locale setting is the currently avtive one, 2013-10-11 entered into the formula bar will always put today's date into a cell, unless it had been formatted as string before the entry.

oCell.getFormula returns the correct string of the un-localized formula, which is "41558".
oCell.getString is completely meaningless. It returns the one formatting variant among thousands of possible formattings for the the number 41558.
oCell.getValue will now return the correct value 41558.
=A1=41558 will return TRUE if the macro wrote to A1 and the NullDate setting is 1899-12-30. This method will put the right date with any other NullDate setting as well.

Similar with a date control. getDate gets/sets the true value as an ISO date YYYYMMDD (integer number with 8 digits). for a programmer the displayed string is completely meaningless.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28429
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Setting date from macro to cell

Postby mr00v3ck » Fri Oct 11, 2013 10:55 am

setFromula did the work perfectly, never have thought.
Thanks a lot!
Win XP SP2 / OpenOffice 3.2.1
mr00v3ck
 
Posts: 11
Joined: Thu May 16, 2013 9:42 am

Re: [Solved] Setting date from macro to cell

Postby Villeroy » Fri Oct 11, 2013 11:52 am

Yes, setFormula("2013-10-11") sets the correct value which will be displayed according to the cell's number format. An unformatted cell (number format "General") will show the correct value as 41558.
FormulaLocal("2013-10-11") does the same but it will also switch the unspecific number format from "General" to the default date format (German 11.10.13, British 11/10/13, US style 10/11/13 or something else).

You could also read the NullDate struct from the document, convert it into a date value of your programming language and then calculate the difference in days between the NullDate and the day you want to write into a cell. The result will be the correct day number.
setFormula(string) or property FormulaLocal performs all this on the fly.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28429
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests