shortcut key to enter current date?

zjcboy · General User Joined: 26 Nov 2003 Posts: 8

what's it? Thanks!

carl · Posted: Wed Nov 26, 2003 6:25 am Post subject:

date()
_________________
carl
Using OpenOffice.org 2 on XP sp2

carl · Posted: Wed Nov 26, 2003 6:27 am Post subject:

sorry its NOW()

which shows how quickly I can forget any and everything
_________________
carl
Using OpenOffice.org 2 on XP sp2

zjcboy · General User Joined: 26 Nov 2003 Posts: 8

well, using now() is ok but here's a problem.

I just want to enter the date in a cell, then let it be when next time I open the file. but if I use now(), it will change from time to time I open the file.

any idea? thanks!

Mike · Guest

Hi!

Create a macro:

Extras/Macro/Macro...

Enter a name for the new macro (for example 'InsertDateIntoCell')

Press the New-Button

Use the following code for the macro:

sub InsertDateIntoCell
'Macro to insert current time into selected cell
'Edited version of the "InsertDateIntoCell" macro from Andrew Pitonyak's Macro document
'Andrew Pitonyak's Macro document can be found at http://www.pitonyak.org/AndrewMacro.sxw (andrew@pitonyak.org)
'Edited by Russ Phillips, 2003-08-13 (avantman42@users.sourceforge.net)
Dim oDesktop As Object, oController As Object, oSelection As Object
Dim doc As Object
oDesktop = createUnoService("com.sun.star.frame.Desktop")
oController = oDesktop.CurrentFrame.Controller
doc = oController.Model
If doc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then
oSelection = oController.Selection
'Set the date value
oFunction = CreateUnoService("com.sun.star.sheet.FunctionAccess")
oFunction.NullDate = doc.NullDate
Dim aEmpty()
oSelection.Value = oFunction.callFunction("NOW", aEmpty())
'Set cell format
oFormats = doc.NumberFormats
dim aLocale as new com.sun.star.lang.Locale
oSelection.NumberFormat = oFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATE, aLocale)
Else
MsgBox "This macro must be run in a spreadsheet document"
End If
end sub

Close the Window (called 'BASIC ....')

Now you can tell OpenOffice to execute your new macro when pressing a keyboard-shortcut. To do so, go to Extras/Macro/Macro..., select your macro and then press the third button (it is called 'Zuordnen...' in the german version, sorry I don't know its english name) and select the tab 'Keyboard'. Then select your macro from the list on the bottom left. You should find it under 'OpenOffice.org/Standard/Module1'. Then select your macro (InsertDateIntoCell) from the list in the middle. Finally assign it any keyboard-shortcut you like.

Greetings

Mike

zjcboy · General User Joined: 26 Nov 2003 Posts: 8

great !!!!!!!!!!!

that's exactly what I want. Thanks!!!

one more question: when I set a shortcut key to the macro, I found I have to choose any of the given ones in the list above. I wonder if I could set any key I'd like ( for example, like in Excel , press Ctrl + ; ) . If so, how can I ?

zjcboy · General User Joined: 26 Nov 2003 Posts: 8

I delete these a few lines:

'Set cell format
oFormats = doc.NumberFormats
dim aLocale as new com.sun.star.lang.Locale
oSelection.NumberFormat = oFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATE, aLocale)

because I don't want it to change the format of a certain cell I've already set. Wink

Guest · Posted: Fri Nov 28, 2003 1:00 pm Post subject:

glad to help you.

I had the same trouble with the impossibility of assigning the 'good old' excel shortcut-key, so I already posted a question asking if this is possible somehow, but I didn't get an answer till now.

If I get one, I'll let you know.

Mike

JohnV · Posted: Fri Nov 28, 2003 2:25 pm Post subject:

I have never seen anyone indicate that you can set up shortcut keys other than those shown on the Keyboard tab. Right or wrong, at least you now have an answer. FWIW, I think it's right unless you do some heavy duty programing of your own.

sinkingfast · Newbie Joined: 07 Mar 2010 Posts: 1 Location: Toronto, ON

And here we are 7 years later using openoffice.org and this macro still works as is. The trick was to get it into a macro because OO doesn't allow you to create a macro a text directly. However you can use the record macro tool to create a test macro & then go tools->macros->organize...->OpenOffice Basic Macros, find the 'test' you created and replace it with the code shown above. Settings to assign it to a shortcut key are different but can easily be done. Many thanks to Mike.
JA

dirkocker · Newbie Joined: 10 Jan 2012 Posts: 1

Hi everybody,

I was also looking for a shortcut to insert Date and Time in Calc in OpenOffice 3.3.0

The solution of Mike is working well for Date. Thanks Mike.
The solution on this site shows some makro code to do in it in a mor flexible way.
http://www.openoffice.org/de/doc/howto/tutorials/howto_insert_date_de.html

Now I just do a copy past from the other site.

Cheers
DO

Villeroy · Posted: Tue Jan 10, 2012 3:29 am Post subject:

The above macro code does not really work. It happens to work if your are running the office under a German locale setting and if you happen to have a single cell selected.

The easiest macro gets the currently active input cell using a helper function getActiveCell. You find it on this forum when you manage to hit the [Search] link on top.
It simply inserts the correct cell value which is a number. It does not touch the formatting, leaving it up to you.