Time stamp creation

Creating a macro - Writing a Script - Using the API

Time stamp creation

Postby abinash1788 » Thu Apr 23, 2020 2:57 pm

I have 3 columns Ticket Id, Start Time, Time Spent. When I enter ticket id then current time should populate under Start time and if ticket id deletes time shouldn't change. Please help with macros.
Open office 4.1.7
Operating System - macOS
abinash1788
 
Posts: 8
Joined: Thu Apr 23, 2020 2:54 pm

Re: Time stamp creation

Postby Villeroy » Thu Apr 23, 2020 7:02 pm

No macro required when using the built-in HSQLDB.
menu:Tools>SQL...
Code: Select all   Expand viewCollapse view
ALTER TABLE "Table Name" ALTER COLUMN "Start Time" SET DEFAULT CURRENT_TIME

menu:View>Refresh Tables
Should work with most database engines other than HSQL as well.
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: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Time stamp creation

Postby abinash1788 » Thu Apr 23, 2020 7:16 pm

Thanks for the reply.
I am working in spreadsheet.
Open office 4.1.7
Operating System - macOS
abinash1788
 
Posts: 8
Joined: Thu Apr 23, 2020 2:54 pm

Re: Time stamp creation

Postby Villeroy » Thu Apr 23, 2020 7:28 pm

Why? If you are able to program macros, you should be able to use the right tool for the job. Professionals do not store lists in spreadsheets.
LibreOffice Calc has a built-in shortcut to insert the time.
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: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Time stamp creation

Postby JeJe » Thu Apr 23, 2020 10:29 pm

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1089
Joined: Wed Mar 09, 2016 2:40 pm

Re: Time stamp creation

Postby joesch » Fri Apr 24, 2020 12:52 am

You can use the following function:

Code: Select all   Expand viewCollapse view
Function wechsel(event)
   ca = event.CellAddress
   With ThisComponent.CurrentController.ActiveSheet
      If ca.Column = 0 AND .getCellByPosition(1, ca.Row).FormulaLocal = "" Then
         .getCellByPosition(1, ca.Row).FormulaLocal = Format(Now(), "DD.MM.YYYY HH:MM:SS")
      End If
   End With
End Function


You must assign this function under SheetEvents...-Content Changed.
Example file attached.


Greetings,
joesch
Attachments
automatic_time.ods
(11.5 KiB) Downloaded 30 times
joesch
 
Posts: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Time stamp creation

Postby Villeroy » Fri Apr 24, 2020 11:12 am

There are tons of timestamp macros on this forum alone.
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: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Time stamp creation

Postby joesch » Fri Apr 24, 2020 11:32 am

There are tons of timestamp macros on this forum alone.


And now there is one more. What's the problem?
"abinash1788" was looking for help and didn't find anything suitable in the forum itself, and I gave him some help.
joesch
 
Posts: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Time stamp creation

Postby Lupp » Fri Apr 24, 2020 12:09 pm

joesch wrote:...And now there is one more. What's the problem?

Did you try this specific macro in a case where you pasted in (Paste Special... Values only e.g.) a RANGE? What about the specifics concerning column, row and FormulaLocal. What intentions behind?

I'm basically interested in macros though I actually use very few of them. Anyway it's amazing how many macros are published without having undergone the obviously needed analaysis and testing. Being too much specialized or having problematic side-effects often is another shortcoming of macros. (This nearly unavoidable in the many cases where macro-thinking goes back to Excel practice.)
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Time stamp creation

Postby JohnSUN-Pensioner » Fri Apr 24, 2020 12:32 pm

Lupp wrote:
joesch wrote:...And now there is one more. What's the problem?

...having problematic side-effects...

In this case, such a side effect (one of many) is the inability to further process the inserted value. We don't have a timestamp, but text that resembles a timestamp.
To use these cells for further calculations, additional steps will be required to convert the text to a date-time value.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Time stamp creation

Postby Lupp » Fri Apr 24, 2020 12:35 pm

Villeroy wrote:LibreOffice Calc has a built-in shortcut to insert the time.

Fully regarding you, of course, I would judge this to be a poisoned hint.

There is no control over the format! You know as well as I do that the locale-dependent default formats for dates (and in some cases also for DOT) aren't only bad but evil mostly. Even the one language/locale I know setting the Y-M-D order by default (Esperanto) spoils next to everything again using 2-digit-year then.
I never use the built in shortcut - and the way it is assigned by default (at least if an en-?? UI is set what everybody should prefer imo if acceptable for him) cannot even be used on a German keyboard e.g.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Time stamp creation

Postby joesch » Fri Apr 24, 2020 12:48 pm

Haben Sie dieses spezielle Makro in einem Fall ausprobiert, in dem Sie (Paste Special... Values only z.B.) einen RANGE eingefügt haben?


No, because it didn't seem necessary to me, because it seemed clear to me that the user wants to enter the first column by keyboard (and wants to save time by adding the date automatically).

What about the specifics concerning column, row and FormulaLocal. What intentions behind?


Row and column resulted for me from the question. FormulaLocal on the other hand, I use to take advantage of the property of Calc that it converts this input (in the concrete case of date/time) into the valid default formatting for date/time (possibly in combination of system settings and settings in OO)

(This nearly unavoidable in the many cases where macro-thinking goes back to Excel practice.)


The error of the Excel macro practice (if you want to call it like that) is imho the adoption of unnecessary .select commands in the macros. Other problems are hardly different from StarBasic, because the languages, StarBasic and VBA, are almost identical (the APIs of course much less).
(I'm not interested in a further discussion about this, because although I support OO on a voluntary basis, I see MS Excel (or MS Office in general) only as a competitor.)


Greetings,
joesch
joesch
 
Posts: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Time stamp creation

Postby joesch » Fri Apr 24, 2020 12:54 pm

In this case, such a side effect (one of many) is the inability to further process the inserted value. We don't have a timestamp, but text that resembles a timestamp.


No, that's not true. By using .FormulaLocal, Calc automatically converts the passed text into a value.
(You can check this by changing the formatting of the cell as a test.)

Greetings,
joesch
joesch
 
Posts: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Time stamp creation

Postby joesch » Fri Apr 24, 2020 1:01 pm

No, that's not true. By using .FormulaLocal, Calc automatically converts the passed text into a value.


A clarification here too. Of course, it is necessary that the format code in:

Code: Select all   Expand viewCollapse view
Format(Now(), "DD.MM.YYYY HH:MM:SS")


is right. My example code refers to German localization.
joesch
 
Posts: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Time stamp creation

Postby Villeroy » Fri Apr 24, 2020 1:14 pm

Your FormulaLocal inserts text unless you are working with a German locale.
oCell.FormulaLocal = Format(today(), "YYYY-MM-DD") will always insert the right value regardless of locale and regardless of the document's NullDate which in rare cases may differ from the default value 1899-12-30
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: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Time stamp creation

Postby joesch » Sun Apr 26, 2020 10:00 am

Villeroy wrote:Your FormulaLocal inserts text unless you are working with a German locale.
oCell.FormulaLocal = Format(today(), "YYYY-MM-DD") will always insert the right value regardless of locale and regardless of the document's NullDate which in rare cases may differ from the default value 1899-12-30


yes, exactly
joesch
 
Posts: 38
Joined: Mon Apr 20, 2020 9:49 am
Location: Germany, near Berlin

Re: Time stamp creation

Postby abinash1788 » Mon Apr 27, 2020 1:43 pm

Thanks for the code.
To assign sheet event its prompting to install JRE file but cant load as system restriction.
Please modify the code where sheet event not required.



joesch wrote:You can use the following function:

Code: Select all   Expand viewCollapse view
Function wechsel(event)
   ca = event.CellAddress
   With ThisComponent.CurrentController.ActiveSheet
      If ca.Column = 0 AND .getCellByPosition(1, ca.Row).FormulaLocal = "" Then
         .getCellByPosition(1, ca.Row).FormulaLocal = Format(Now(), "DD.MM.YYYY HH:MM:SS")
      End If
   End With
End Function


You must assign this function under SheetEvents...-Content Changed.
Example file attached.


Greetings,
joesch
Open office 4.1.7
Operating System - macOS
abinash1788
 
Posts: 8
Joined: Thu Apr 23, 2020 2:54 pm

Re: Time stamp creation

Postby Mr.Dandy » Mon Apr 27, 2020 2:01 pm

JRE is needed for macro
OpenOffice 4.1.7 - Windows 10
User avatar
Mr.Dandy
 
Posts: 314
Joined: Tue Dec 11, 2012 4:22 pm

Re: Time stamp creation

Postby Villeroy » Mon Apr 27, 2020 2:04 pm

Ignore that warning. It is pointless. You do not need any Java to run Basic or Python macros. The macro and the assignment to the sheet event will work anyway.
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: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Time stamp creation

Postby abinash1788 » Mon Apr 27, 2020 2:08 pm

While running the macro, time is changing if I change respective data.
Please find the attachment.
I want to add RAP ID and time should populate under Start Time . If I delete or modify the Rap ID , time is changing . Time shouldn't change. It should be static. Please check
Attachments
POI_Tracker.ods
(30.2 KiB) Downloaded 26 times
Open office 4.1.7
Operating System - macOS
abinash1788
 
Posts: 8
Joined: Thu Apr 23, 2020 2:54 pm

Re: Time stamp creation

Postby Villeroy » Mon Apr 27, 2020 3:46 pm

Test if the cell's formula is "" or the value =0
Works for me. Spoon feeding macro code again.
Attachments
POI_Tracker2.ods
(23.51 KiB) Downloaded 29 times
Last edited by Villeroy on Mon Apr 27, 2020 3:59 pm, edited 3 times in total.
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: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Time stamp creation

Postby JohnSUN-Pensioner » Mon Apr 27, 2020 3:54 pm

...and verify columns numbers - index of column F (RAP ID) is 5, not 6, index of column H (Start Time) is 7, not 9
By the way, if you will change cell value with macro then you not need formulas in column H
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Time stamp creation

Postby abinash1788 » Tue Apr 28, 2020 9:12 am

The sheet is protected and macro is not running . Please suggest what to do. Thanks in advance.
Open office 4.1.7
Operating System - macOS
abinash1788
 
Posts: 8
Joined: Thu Apr 23, 2020 2:54 pm

Re: Time stamp creation

Postby Lupp » Tue Apr 28, 2020 10:57 am

If the cells you want to enter something are protected, you can't. That's the substance of "protection". Switch it off for the cells needing to accept editing. Protection properties are part of the cell style.

The cells a macro is writing to may be protected. This will not interfere with the macro action.
<edit 2020-04-28 about 10:50 UTC>
The above statement was based on my experiences with recent LibreOffice 6.4.3.
This behavior may be seen as a bug, but I won't report it. I prefer the new situation.
(The "new" behavior is inconsistent, however.)
Concerning my AOO 4.1.7 my test found the "old" behavior:
Protected cells in protected sheets are locked also against macros.

Anyway: You cannot make spreadsheets foolproof or hardened against attacks by users.
Insisting on a "hard" protection you need to use a database with a full-grown rights management.
</edit>

You cannot use macros without understanding them basically.
Same with protection and with form controls.
There are guides.
Concerning macros there are also the famous texts by Andrew Pitonyak.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2897
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Time stamp creation

Postby Villeroy » Tue Apr 28, 2020 10:01 pm

If you have to do that in a spreadsheet, you better install MS Excel.
If you want to do that with a tool of your choice, you better learn how to use a databaes.
Knowledge is power.
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: 28441
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: Google [Bot] and 2 guests