I do not turn off the computer for several days in a row, and the calc table with macros continues to be on the screen. But I need to catch the moment of transition to the next day and run some macro for the corresponding changes in the table. Please give me some ideas how to write a macro to catch the transition to the next day, with an accuracy of several minutes.
I donât feel sure:
Do you want to keep LibreOffice running with an open Calc document, and to get some action triggered by a system-timer?
If so, Iâm afraid LibO doesnât offer the needed service. I would assume you need to start LibO from a program capable of handling such timers, and of triggering action in your sheets via simulated keyboard input.
I never did something this way, and using my search program on the web I didnât find something that would suit me.
Why do you think the wanted âcorresponding changesâ must be made real-time? Why not save a few lines of plain text to a csv-style file, and let Calc work with that file âonREOpenâ later?
@Lupp, thanks for your comment. For some reason I donât want to reopen calc table. I can create a button with macro to check current date, compare it to the date remembered before and then make corresponding changes. I wanted to do it without any buttons, but you wrote that LibO doesnât offer the needed service. Okay, Iâll do it with the button. Thanks anyway.
There is a trick that will allow you to implement this project.
Create a new spreadsheet. It will be a very small spreadsheet in which you write one =NOW() formula. Give this cell a name such as CurrentDateTime. Save this table in a convenient location.
Now add a helper sheet to your main spreadsheet and fill in two cells. In the first of them, for example, in B1, insert a link to the cell CurrentDateTime (Ńhoose Sheet - Link to External Data)
In the adjacent cell, write down a formula that, when the desired condition is met, will run your macro, something similar to =IF(INT(B1)=INT(DoSomething.$B$1);"Wait...";MY_MACRO())
Of course, MY_MACRO() should be a function that will perform all the planned actions, return some value to display in the cell (for example, the text âworked!â) And - IMPORTANT! - will change the value in the cell DoSomething.$B$1. Since this cell is on a neighboring sheet, the function will be able to change it - usually such actions are not recommended, but since you know exactly what you are going to do, you have every right to do so.
2 Likes
Youâre great, old friend.
However, I would suggest not to use named ranges in external references. (Bad experiences - long ago.)
There is no other way than using a named range with external data sources.
Of course, you are right.
Sorry! I always tried to avoid links to external data, and lacking sufficient experience, I messed up linkage to external âtablesâ in different spredasheet documents with direct references to such ranges occurring in ordinary formulas.
There is another solution: create a link to an external file, and then delete it. The link will stop working and generate an error. However, the link update will be called regularly. This will lead to the correct recalculation of the function =NOW() in the current spreadsheet, and the result of this function is used as a timer. This eliminates the need for a second file. I donât really like this decision - to feed Calc with a deliberate mistake? No, do not like itâŚ
You can resist everything except temptation?
Of course! Like any programmer, Iâm lazy. Laziness is a great engine of progress: it helps to resist everything ⌠and even some not very strong temptations 
To comfort you concerning your remorses:
Links of the used kind are presented as Links to External Files by the UI. They simply are Links to Named Ranges, and such a link works the same way -including the automatic update- if the range is internal, even if its output goes to the same sheet.
Going this way you feed two birds with one breadcrumb: You have your timer, and you feel comfortable having avoided the fake link. I just tested it, and would like to have you also testing it with the attached file. The update interval is set to 10 s. It should work in every location.
aTimerThing.ods (9.4 KB)
Nothing like the interaction of two lazy programmers? I never would have tested this without being egged by your idea.
BTW: If you rename the file, you will -sadly- get a zombie link as with a removed source file.
1 Like