Using Macros to Automate OpenOffice.org Calc
How to Create an OpenOffice Macro
OpenOffice.org is fast becoming, if not a replacement then, at least an alternative to Microsoft Office. Like Microsoft Office OpenOffice.org has:
- a spreadsheet (Calc)
- a word processor (Writer)
- a database (Base)
- it's own programming language - OpenOffice.org Basic.
With OpenOffice.org Basic it is possible for anyone to extend the default functionality and automate any essential or repetitive tasks.
OpenOffice.org Basic Macro Organization
Before creating an OpenOffice.org basic macro the new user needs to understand a little about how macros are organized:
- macros are stored a module
- modules are stored in a library
- libraries belong to one of three groups:
- My Macros; the users main area for storing macros and any macro stored here will be available to all of the user's documents
- OpenOffice.org Macros; the user does not usually have access to this area and it is used for any system wide macros.
- the OpenOffice.org document itself; only used if the document is likely to be passed on to another user who may need to run the macro. Macros stored here are available to the one document and only that document
Bearing all of this in mind the new user is ready to create their first macro.
Creating a New OpenOffice.org Basic Macro
OpenOffice.org Basic has its own built in IDE (Integrated Development Environment). So if Calc, for example, is open then the first steps to creating a new macro are:
- go to the main menu bar and click on:
- Tools
- Macros
- Organize Macros
- OpenOffice.org Basic
OpenOffice.org will now display the 'OpenOffice.org Basic Macros' dialog box and the new user must decide which area to use for the macro (i.e. 'My Macros' or the document itself) and then pressing the 'New' button. At this point a new macro will be created and the IDE will open up in edit mode.
It's worth noting that OpenOffice.org always creates a subroutine called 'Main', but this has nothing to do with the 'Main' subroutine seen in programming languages such as C (where 'Main' is the subroutine that is run by default); OpenOffice.org always runs the first macro that it comes to, regardless of its name.
The real strength of the OpenOffice.org Basic macro is when it comes to automating tasks that would otherwise take up valuable time and effort (although it's always worth considering whether or not the time spent coding outweighs the time that it's meant to save); and the required automation can be achieved with relatively few, simple concepts.
Writing OpenOffice.org Basic Macro Code
Any OpenOffice.org application consists of a number of objects and functions:
- thisComponent - the current document (normally the last one opened)
- Sheets - the individual sheets making up the spreadsheet (with Sheet(0) being the first sheet, Sheet(1) being the second, etc, etc);
- getCellRangeByName - a function that selects a defined cell in a spreadsheet
The end result is a working macro:
Sub Main
Dim oSheet
oSheet = thisComponent.Sheets(0)
oSheet.getCellRangeByName("A1").String = "Input Data"
oSheet.getCellRangeByName("A2").Value = 4
oSheet.getCellRangeByName("A3").Value = 5
oSheet.getCellRangeByName("A4").Formula = "=A2*A3"
End Sub
Running an OpenOffice.org Basic Macro
The macro is run by pressing the 'Run Macro' button (the one with the green triangle) and (assuming all the lines have been entered correctly) then the spreadsheet will have:
- text saying "Input Data" in A1
- the number 4 in A2
- the number 5 in A3
- a formula displaying the result 20 in A4
This is, of course, a very simple example, but does show how easily tasks can be automated within OpenOffice.org.
