Calc - Macro: Enter Date as a Date

[shecki]

Hi,

is there a way to enter a date to a cell from a macro that is then also in the Format as a Date, so that Calc can use it as a date?

To give an example:
I use this in my macro:
mycell.String = Format(Now, “dd.mm.yy”)

That places the date as a String in my Sheet. If I try to use that further in the sheet as a date, that doesn’t work, because the sheet now thinks, that this is a string. Formatting by hand adds an annoying little ’ to the string and the then doing that for several thousand entries… well that’s what the macro is for.

So how can I enter this, so that it is a date?

[JohnSUN]

@shecki

mycell.String = Format(Now, “dd.mm.yy”)

Why not mycell.Value = Now ?
(Actually, it is correct to write mycell.setValue(Date()) - the meaning of the action is the same, but it is more correct this way)
The fact that a number appears in the cell, for example, 44989, then this is already a matter of formatting, and not inserting a value.

[karolus]

Hallo
btw: there is a shortcut to insert current Date|Time:
<ctrl>, for the Date,
<ctrl><shift>; for the Time and
<ctrl>,<ctrl><shift>; for current DateTime

[shecki]

Well you are right, the cells were formatted as text, if I change that to Date for all cells, then mycell.value = Now enters a correct date, but with unneeded Hour:Minute:Second.

If I try my Format above (dd.mm.yy) to get an European (or German) Format to the date, then a complete wrong date is entered (25.5.10 instead of 04.03.23)

But since your suggestion can be used as a date, I’ll take that and proceed with my List

Thx so far for that.

[JohnSUN]

@shecki If you take off from @karolus’s hint and look into Tools-Customize, you’ll be able to write something like this when you see this.

	With ThisComponent.CurrentController
		.Select(mycell)
		createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(.Frame, ".uno:InsertCurrentDate", "", 0, Array())
		.Select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
	End With 

In this case, the formatting issue will disappear (but there will be a question about saving the previous cursor position)

[Villeroy]

Working with styles makes everything easier in the UI and when writing macros.
oCell.setValue(Now())
oCell.CellStyle = “My_Sophisticated_Cell_Style”