Create a Date Stamp Shortcut in Calc:

Write A Macro to Insert the Date in OpenOffice.org Calc.

Apr 17, 2009 Brendan Vittum

This article is a quick tutorial on how to create a Macro in OpenOffice.org Calc to automatically insert the current date in a cell with a keyboard shortcut.

OpenOffice.org Calc, or just Calc, is the spreadsheet software that comes with the OpenOffice.org productivity suite.

Calc is very much like Microsoft Excel, with an equivalent set of features, but it also provides a number of features not present in Excel.. Calc is can open and save most spreadsheets in the Microsoft Excel file format, as well as a great many other formats.

There is one thing Calc does not do that Microsoft Excel does natively. Pushing the control key (Ctrl) and the plus key (+) keys simultaneously in Excel inserts the current date; differing from TODAY and NOW which insert data which is updated when the file is accessed.

To insert a date stamp in Calc:

There is a very quick, easy fix for the lacking ability to insert a date stamp in Calc though. Instead of having to laboriously type the date every time, one can simply write a macro which can inserts the data with a keystroke just as Excel does.

Launch OpenOffice.org Calc.

Click Tools → Macros → Organize Dialogues on the menu.
In the window which opens click the Libraries tab.
Click the New button.
Enter a name for the New Library like myAutoDate.
Press the OK button.

The new library item just created should appear in the list in th Library window and be highlighted. If not simply select it with the mouse and click Edit which opens the BASIC editor for creating Macros in OpenOffice.org. Select the lines of code in your editor window after the line starting with REM and delete them, Now, paste the following Macro code into the BASIC editor:

Write the Macro to Insert a Date Stamp in Calc

The following macro can then be used to insert the date stamp.

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

(The Macro code can be downloaded from http://wwwebwarrior.com/calc_date_macro.txt if it does not render correctly in your browser.)

Assign a keystroke to the date stamp Macro in Calc

Now click File → Save in the menu, then File → Close which goes back to the blank spreadsheet. The next step is to assign the keystroke that will call the Macro. This is done by clicking on Tools → Customize in the menu, then selecting the Keyboard tab in the open window.

In the lower left of this window there are three boxes labeled Categories, Function, and Keys. Under Categories find the entry towards the bottom named OpenOffice.org Macros → user → myAutoDate → myAutoDate. myAutoDate is the name assigned in step four above; myAutoDate in this example.

In the portion above Categories, Functions, and Keys is an area labeled Shortcut Keys with a lengthy list of available shortcuts and their assigned actions. Find one in the list which is easy to remember and highlight it, then click Modify. In the case of this example Control Enter (Ctrl+Enter) was chosen.

Click OK to return to the blank spreadsheet and push the keystroke just assigned. If everything worked correctly today's date should now fill the cell!

The copyright of the article Create a Date Stamp Shortcut in Calc: in Computer Software is owned by Brendan Vittum. Permission to republish Create a Date Stamp Shortcut in Calc: in print or online must be granted by the author in writing.

OpenOffice.org Macro Organizer.	OpenOffice.org BASIC Editor.	OpenOffice.org Keyboard Shortcut Customization 1
OpenOffice.org Keyboard Shortcut Customization 2