Ask Your Question
0

How do I create a macro to insert the time into a cell in Calc?

asked 2016-07-13 15:59:31 +0200

NancyHT gravatar image

In my time-tracking spreadsheet I was able to click shift and ":" to insert the current time into a cell. How can I duplicate same in Libre Office Calc? I can insert the time into a cell but it takes several clicks to do so, I was able to do this in Excel with one keystroke.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2016-07-13 16:06:44 +0200

erAck gravatar image

updated 2016-07-13 16:08:03 +0200

There are shortcuts to insert current date and/or time into a cell, in English UI it's Ctrl+; for date and Shift+Ctrl+; (or Ctrl+:) for time. Date or time are added to already existing time or date cell content, and overwrite other cell content, unless in cell edit mode where the date or time is simply inserted at the current cursor position.

edit flag offensive delete link more
0

answered 2016-07-13 16:49:31 +0200

mark_t gravatar image

Two possible methods depending if you just want a string representing the time or if you want it inserted as a time that can be used in calculations.

Simple version as a string:-

REM  *****  BASIC  *****

Option Explicit

Dim FuncService As Object

Sub AddCurrentTimeString()

    Dim oCurSelection As Object

    oCurSelection = thisComponent.CurrentSelection

    If oCurSelection.ImplementationName() = "ScCellObj" Then
        '   Create service to access sheet functions, If it doesn't already exist
        If FuncService Is Nothing Then FuncService = createunoservice("com.sun.star.sheet.FunctionAccess")

        '   Insert as a text string
        oCurSelection.String = Format(FuncService.CallFunction("now",  Array()), "HH:MM:SS")
    Else
        Msgbox "This macro is only allowed for a single cell selection."
    End If
End Sub

If you want it inserted as a time then use:-

REM  *****  BASIC  *****

Option Explicit

Dim FuncService As Object

Sub AddCurrentTime()

    Dim oCurSelection As Object

    oCurSelection = thisComponent.CurrentSelection

    If oCurSelection.ImplementationName() = "ScCellObj" Then
        '   Create service to access sheet functions, If it doesn't already exist
        If FuncService Is Nothing Then FuncService = createunoservice("com.sun.star.sheet.FunctionAccess")

        '   Cannot set the NumberFormat correctly without first making sure it exists
        oCurSelection.NumberFormat = FindCreateNumberFormatStyle("HH:MM:SS")

        '   Insert as a text string
        oCurSelection.Value = FuncService.CallFunction("now",  Array())
    Else
        Msgbox "This macro is only allowed for a single cell selection."
    End If
End Sub

Function FindCreateNumberFormatStyle (sFormat As String, Optional doc As Object, Optional locale As com.sun.star.lang.Locale) As Integer
'Author: Andrew Pitonyak
'email:   andrew@pitonyak.org 

    Dim oDoc As Object
    Dim aLocale As New com.sun.star.lang.Locale
    Dim oFormats As Object
    Dim formatNum As Integer

    oDoc = IIf(IsMissing(doc), ThisComponent, doc)

    oFormats = oDoc.NumberFormats()

    '   If you choose to query on types, you need to use the type 
    '   com.sun.star.util.NumberFormat.DATE
    '   I could set the locale from values stored at
    '   http://www.ics.uci.edu/pub/ietf/http/related/iso639.txt 
    '   http://www.chemie.fu-berlin.de/diverse/doc/ISO_3166.html 
    '   I use a NULL locale and let it use what ever it likes.
    '   First, see if the number format exists

    If Not IsMissing(locale) Then aLocale = locale

    formatNum = oFormats.queryKey (sFormat, aLocale, TRUE)

'   MsgBox "Current Format number is" & formatNum       '   Debug only

    '   If the number format does not exist then add it

    If (formatNum = -1) Then
        formatNum = oFormats.addNew(sFormat, aLocale)

        If (formatNum = -1) Then formatNum = 0

'       MsgBox "new Format number is " & formatNum      '   Debug only
    End If

    FindCreateNumberFormatStyle = formatNum 
End Function

Modify the string format "HH:MM:SS" in the above if you want to alter the format, example to include date use "D MMM YYYY HH:MM:SS".

Either of above could be assigned to a ctrl character using "Tools", "Customize...".

edit flag offensive delete link more

Comments

You do not need a FunctionAccess service in this case. There is a BASIC runtime function Now returning the date-time value as Double. See this info.

Lupp gravatar imageLupp ( 2016-07-13 20:01:14 +0200 )edit

These BASIC macros really are not needed at all unless you use an outdated LibreOffice version prior to 4.4 or some such. The keyboard shortcuts for date and time I mentioned in my answer work better when combining date and time stamps and also apply the correct number formats, depending on locale and taking formats already applied to the cell into account, even when editing within the cell text.

erAck gravatar imageerAck ( 2016-07-14 19:32:03 +0200 )edit

@erAck: You are right, of course. I did not basically want to encourage the usage of a macro for the task. I simply pointed at the existence of the Now() function in BASIC.

Lupp gravatar imageLupp ( 2016-07-14 23:04:47 +0200 )edit

Thank you both. I didn't realize that these shortcuts existed and as the OP had asked for macros I posted modified code from a previous basic macro that was time stamping data collection. Also thanks for info on the basic function, I'll remember to check in future if basic has the worksheet function I'm trying to use.

mark_t gravatar imagemark_t ( 2016-07-15 14:51:24 +0200 )edit
0

answered 2016-07-13 16:18:59 +0200

NancyHT gravatar image

Yep that worked, thanks. I think I forgot about using the control key. Duh.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-07-13 15:59:31 +0200

Seen: 1,013 times

Last updated: Jul 13 '16