[Solved] Put date in cell with CellContentType as Value

[luofeiyu]

With the following code, “2025-09-01” was shown in message box,but the content in cell “a1” is “2025” , its CellContentType is value.

Code: Select all

Sub WriteFormattedDateTime()
    Dim oSheet As Object
    Dim oCell As Object
    Dim sFormattedDate As String
    oSheet = ThisComponent.Sheets.getByName("Sheet1")
    oCell = oSheet.getCellByPosition(0, 0)  
    sFormattedDate = Format(Now(), "YYYY-MM-DD")
    msgbox sFormattedDate
    oCell.value = sFormattedDate
End Sub

With the following code, “2025-09-01” was shown in message box,but the content in cell “a1” is “2025-09-01” , its CellContentType is text.

Code: Select all

Sub WriteFormattedDateTime()
    Dim oSheet As Object
    Dim oCell As Object
    Dim sFormattedDate As String
    oSheet = ThisComponent.Sheets.getByName("Sheet1")
    oCell = oSheet.getCellByPosition(0, 0)  
    sFormattedDate = Format(Now(), "YYYY-MM-DD")
    msgbox sFormattedDate
    oCell.string = sFormattedDate
End Sub

How can write whole date “2025-09-01” into cell and set CellContentType as value ?

[FJCC]

This code sets the value of A1 to today's date value and sets the number format to YYYY-MM-DD. Is that what you want?

Code: Select all

Sub WriteFormattedDateTime()
    Dim oSheet As Object
    Dim oCell As Object
    Dim sFormattedDate As String
    Dim Locale as New com.sun.star.lang.Locale
    oSheet = ThisComponent.Sheets.getByName("Sheet1")
    oCell = oSheet.getCellByPosition(1, 0)  
 
    DateVal = int(Now())
    
    Locale.Country = "US"
    Locale.Language = "en"
    
    FormatNumber = ThisComponent.NumberFormats.queryKey("YYYY-MM-DD", Locale, True)
    'msgbox FormatNumber
    oCell.value = DateVal
    oCell.NumberFormat = FormatNumber
End Sub

[Zizi64]

...Or you can set a predefined cell style by your macro:

Code: Select all

Sub WriteFormattedDateTime()
 Dim oSheet As Object
 Dim oCell As Object
 
	oSheet = ThisComponent.Sheets.getByName("Sheet1")
	oCell = oSheet.getCellByPosition(0, 0)  
	oCell.value =  Now()
	oCell.CellStyle = "MyDateTime"
End Sub

I suggest you to use Cell Styles instead of the direct formatting method.

SetCellStyle.ods

(10.27 KiB) Downloaded 2 times

[karolus]

Crosspost without Reference