Create a Date Stamp Shortcut in Calc: Write A Macro to Insert the Date in OpenOffice Calc

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

1. Click Tools → Macros → Organize Dialogues on the menu.
2. In the window which opens click the Libraries tab.
3. Click the New button.
4. Enter a name for the New Library like myAutoDate.
5. 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
  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

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!

---

Posted on Utopian.io - Rewarding Open Source Contributors

---