Adding an OpenOffice.org macro is a very useful way of extending the functionality of a Calc spreadsheet or a Writer document. For example, a user may wish to time stamp a spreadsheet every time that it's opened - a task that can easily be achieved by adding a macro which the user manually runs whenever they open the spreadsheet. Of course, there are some drawbacks to this:
- the user must remember to run the macro
- the user must know how to run the macro
- the user must want to run the macro.
A much better solution for everyone is to get OpenOffice to do all of the hard work itself - and for that macros can be assigned to events.
OpenOffice.org's Built-in Events
OpenOffice has a number of built-in events that can be used to trigger any macro; these events are:
- activate document
- close application
- close document
- create document
- deactivate document
- document has been save as
- document has been saved
- document is closing
- 'Modified' status was changed
- open document
- print document
- save document
- save document as
- start application
So if, for example, the user wants the macro to run whenever they open the document then the trigger event should be 'open document'.
Having decided which is to be the trigger event then next step is to create a macro for that event.
A Time Stamp Macro
The whole purpose, obviously, is to run a macro when a particular event occurs, in this case the macro is one which will record:
- the current date and time
- the date and time that the file was last modified
- the user name of the person carrying out the modification
So, the next job to do is to build the macro which will do just that.
'Ensure that all variables are defined
Option Explicit
'Define the start the subroutine
Sub set_details
'Define the variables to be used in the subroutine
Dim Cell, Sheet, ModDate
'Select the first sheet in the current spreadsheet
Sheet = thisComponent.Sheets(0)
'Place the word "Date" in cell A1
Cell = Sheet.getCellRangeByName("A1")
'Place the actual date in B1
Cell.String = "Date"
Cell = Sheet.getCellRangeByName("B1")
Cell.String = Format (Date, "YYYY-M-D")
Cell =Sheet.getCellRangeByName("C1")
Cell.String = "Time"
'Put the time in D1
Cell = Sheet.getCellRangeByName("D1")
Cell.String = Time
'Now insert the current user name
Cell = Sheet.getCellRangeByName("A2")
Cell.String = "Updated by"
Cell = Sheet.getCellRangeByName("B2")
Cell.String = environ ("USER")
'Finally insert the last modified date
'If the the file is new then use the creation date
if isObject ( thisComponent.DocumentInfo.ModifyDate) then
ModDate = thisComponent.DocumentInfo.ModifyDate
else
ModDate = thisComponent.DocumentInfo.CreationDate
end if
Cell = Sheet.getCellRangeByName("A3")
Cell.String = "Last updated"
Cell = Sheet.getCellRangeByName("B3")
Cell.String = ModDate.Year & "-" & ModDate.Month & "-" & ModDate.Day
'And end the subroutine
End Sub
If a user runs this macro manually (after opening a spreadsheet) then the result in the spreadsheet would be something like:
Date_____________|2008-7-28|Time|12:40
Updated by______|bainm____|_____|
Last Update Date|2008-7-27|Time|16:26
Assigning a Macro to an Event
Writing (and, of course, testing) the macro is actually the most complicated part of the process. The next (and easiest) step is to assign the macro to an an event; to do this the user must:
- click on Tools
- click on Customize
- click on the Events tab
- select the required event (e.g. Open Document)
- click on Macro
- select the required macro
And then, the next time that the user opens the document the macro will run automatically.
Mark Alexander Bain - Mark Alexander Bain is a writer, Mo Bro and consultant for all aspects of software development at dsquared. He has also written regularly ...
