[Solved] Setting date from macro to cell

[mr00v3ck]

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

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?

[Villeroy]

The easiest way to write a "date value" into a cell is:

Code: Select all

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.

[mr00v3ck]

setFromula did the work perfectly, never have thought.
Thanks a lot!

[Villeroy]

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.