[Solved] Need help with creating date macro

Creating a macro - Writing a Script - Using the API

[Solved] Need help with creating date macro

Postby gibsongk55 » Fri May 15, 2020 12:09 pm

Hi,

I followed the instructions in this forum https://forum.openoffice.org/en/forum/viewtopic.php?t=100724

and I setup a keyboard shortcut. Only problem is the next day the date has changed to the current date.

As the tutorial says
One can use recorded macros to put the current date or time in a cell as a constant, a timestamp, unlike the =TODAY() and =NOW() functions which change their value dynamically.


But what it doesn't say is what to change the =NOW() function to in order to get a fixed date inserted into the cell?

Thanks for any help,

Gibs
Last edited by MrProgrammer on Sat May 23, 2020 8:29 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OOo 4.1.X on MS Windows 7 UltimateX64, Open Office Calc
gibsongk55
 
Posts: 43
Joined: Sun Nov 30, 2008 12:44 am

Re: need help with creating date macro

Postby Zizi64 » Fri May 15, 2020 12:55 pm

1. You can write a macro for this task.

2. Copy the content of the cell containing the =NOW() (or = TODAY() ) function, and use the "paste special as unformatted text" in same cell (or into other cells) .

3. Otherwise the LibreOffice has such feature.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9430
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: need help with creating date macro

Postby Villeroy » Fri May 15, 2020 1:00 pm

If you follow all steps while recording a macro, you should get a constant date/time value in the active cell and the recorded macro should replay these steps.

Here are another three hand written macros (not recorded):
Code: Select all   Expand viewCollapse view
Sub DateToActiveCell()
  c = getActiveCell(ThisComponent.getCurrentController())
  n = Now()
  c.FormulaLocal = Year(n)&"-"& Month(n)&"-"& Day(n)
End Sub

Sub TimeToActiveCell()
  c = getActiveCell(ThisComponent.getCurrentController())
  n = Now()
  c.FormulaLocal = Hour(n)&":"& Minute(n)&":"& Second(n)
End Sub

Sub DateTimeToActiveCell()
  c = getActiveCell(ThisComponent.getCurrentController())
  n = Now()
  c.FormulaLocal = Year(n)&"-"& Month(n)&"-"& Day(n)&" "& Hour(n)&":"& Minute(n)&":"& Second(n)
End Sub

Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
   as1()  = Split(oView.ViewData, ";")
   lSheet = CLng(as1(1))
   sDum = as1(lSheet +3)
   as1() = Split(sDum, "/")
   on error goto errSlash
      lCol = CLng(as1(0))
      lRow = CLng(as1(1))
   on error goto 0
   getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
   if NOT(bErr) then
      bErr = True
      as1() = Split(sDum, "+")
      resume
   endif
End Function


Hint: Apache OpenOffice is a dead project. If you install its successor https://libreoffice.org/ you get predefined shortcuts to insert date and time into a Calc cell.
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: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: need help with creating date macro

Postby gibsongk55 » Fri May 15, 2020 1:07 pm

Hi,

Thanks for the reply. Just not sure i follow it. This is what i got from recording the macro and following the original instructions:

Code: Select all   Expand viewCollapse view
sub Ins_Curr_Date
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "StringName"
args1(0).Value = "=NOW()"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args1())


end sub


Now is there a simple way to make the date fixed?
OOo 4.1.X on MS Windows 7 UltimateX64, Open Office Calc
gibsongk55
 
Posts: 43
Joined: Sun Nov 30, 2008 12:44 am

Re: Need help with creating date macro

Postby Zizi64 » Fri May 15, 2020 3:22 pm

Why you want to use macros if you have not skills for the programming?
Use the LibreOffice. It has fixed date inserting feature.

Try to modify the recorded macro:

Code: Select all   Expand viewCollapse view
args1(0).Name = "StringName"
args1(0).Value = str(NOW)


The NOW is the StarBasic command for getting the actual date-time value. And the str() function converts the value to a string. A string will not be "refreshed" in the cell. And finally the number recognition feature of the Calc will convert the inserted text to a fixed, formatted numeric value, in other words: to a TIMESTAMP.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9430
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Need help with creating date macro

Postby gibsongk55 » Fri May 15, 2020 3:39 pm

Thank you, I will try this. On a side note i keep getting told to use LibreOffice. Well today I did just that. The calc files i have with lots of custom filtering does not work at all. I spent a lot of money and time getting that done. So i uninstalled LibreOffice today as well. Tonight when i got back home and booted the computer something was strange... windows configuring....

I was right something very strange LibreOffice uninstalled my Microsoft Office Professional. Really really really pissed me off. Restoring would not work. Spent over an hour to get it back with all my important emails. Never will i use LibreOffice ever again. Downright criminal what they did. I did a search and people started having this same problem since 2015 and obviously they don't give a crap about fixing something they are well aware of.

Thanks again for your help Sir.
OOo 4.1.X on MS Windows 7 UltimateX64, Open Office Calc
gibsongk55
 
Posts: 43
Joined: Sun Nov 30, 2008 12:44 am

Re: Need help with creating date macro

Postby RoryOF » Fri May 15, 2020 3:42 pm

It may be that LibreOffice only changed the File Associations for the file types that were handled by MS Office. These are simple to restore.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31232
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Need help with creating date macro

Postby gibsongk55 » Fri May 15, 2020 4:11 pm

No it actually uninstalled Microsoft Office. When opening any MS office programs (not files) it opens installer.
OOo 4.1.X on MS Windows 7 UltimateX64, Open Office Calc
gibsongk55
 
Posts: 43
Joined: Sun Nov 30, 2008 12:44 am

Re: Need help with creating date macro

Postby Zizi64 » Fri May 15, 2020 5:25 pm

I was right something very strange LibreOffice uninstalled my Microsoft Office Professional.

It is impossible. The LO can not uninstall other softwares. If something did it, that was not the LibreOffice.

You can install the Apache OpenOffice, the LibreOffice and the MS Office paralell. Of course you (or the installer packs) can assign only one application to the document types. It will be the lastly installed application - if you have not overturned the automatic processes during a custom installation.

I had use an old MSO 2002, the LO and the AOO in same time on my old Win7 PC. Now I am using the LibreOffice only. (And there is AOO portable version on my PC.)
Last edited by Zizi64 on Fri May 15, 2020 5:36 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9430
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Need help with creating date macro

Postby RoryOF » Fri May 15, 2020 5:33 pm

How are you opening the MS Office programs? By trying a doubleclick on a file, or by opening from Programs on the Start button or a dedicated icon pointing to the MS Office? If the Programs or a dedicated icon, check the target as shown in the text of the button or of the Programs link. If by double clicking a file, then the File Association may have been reset incorrectly by whatever.

 Edit: I wish to confirm: you are using Windows 7? 
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31232
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Need help with creating date macro

Postby Villeroy » Fri May 15, 2020 6:58 pm

When installing either OpenOffice or LibreOffice you are asked if the program to be installed should take over file associations of Microsoft Office documents, if the program should open doc(x), xls(x) and ppt(x) files on double-click. You can even distinguish between Word, Excel, Powerpoint and Visio files. You have chosen that LO should be the default program for MS Office files. When you double-click such a file, LO will be used to open it. Your free choice. We are living in the year 2020, 25 years after Windows '95. You can open any type of file with any program installed on your computer if that program is designed to handle that type of file one way or the other. If you still don't know about programs, file types and file associations mean and if double-clicking on icons is your one and only way to open a file, then you need a computer course more than anything else.
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: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need help with creating date macro

Postby MrProgrammer » Fri May 15, 2020 7:59 pm

Thank you for your question. It has revealed a mistake in [Tutorial] Favorite Recorded Calc Macros.

gibsongk55 wrote:Only problem is the next day the date has changed to the current date.
Tutorial wrote:One can use recorded macros to put the current date or time in a cell as a constant, a timestamp, unlike the =TODAY() and =NOW() functions which change their value dynamically.
But what it doesn't say is what to change the =NOW() function to in order to get a fixed date inserted into the cell?
The instructions for the DateStamp and TimeStamp macros after Next → OK were supposed to convert the function to a fixed value, however the choice for Selection was wrong. The tutorial has been updated. The correct Selection choice is Numbers, as shown below. Please give this a try. The macro should then convert the NOW() [or DATE()] function to a fixed value using the Copy and Paste Special features.

TimeStamp  Puts the current date/time  Insert → Function → Category:Date&time    
in the selected cell → Function:NOW → Next → OK → Edit → Copy
→ Edit → Paste Special → Selection:Numbers
→ Options:None → Operations:None
→ ShiftCells:Don't → OK

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3982
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests