[Solved] Need help with creating date macro

[gibsongk55]

Hi,

I followed the instructions in this 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

[Zizi64]

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.

[Villeroy]

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

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.

[gibsongk55]

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

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?

[Zizi64]

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

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.

[gibsongk55]

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.

[RoryOF]

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.

[gibsongk55]

No it actually uninstalled Microsoft Office. When opening any MS office programs (not files) it opens installer.

[Zizi64]

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.)

[RoryOF]

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?

[Villeroy]

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.

[MrProgrammer]

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

Only problem is the next day the date has changed to the current date.

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.