Automate OpenOffice.Org Calc with Macros

If you're already an OpenOffice.org user then you'll know that Sun's free and open source alternative to Microsoft office can drastically improve your productivity saving you both time and money. However, you'll also be aware that some tasks can still be laborious and time consuming - and that's where OpenOffice.org macros come in. In this tutorial we'll see how macros can take some of the leg work out of those tedious day-to-day tasks; and, in particular, you'll learn how to use a macro to change the data in some cells in an OpenOffice.org Calc document.

When you come to write your macro you'll be using a programming called Basic, but don't worry you're not going to suddenly turn into a computer geek - all you'll need to do is to use some simple commands; and you'll do all of this through OpenOffice.org's Basic IDE (Integrated Design Environment); again, don't worry - you won't have to install any extra, complicated software - this all comes built into the OpenOffice.org application.

Storing Macros

Before creating your first macro you just need to understand a little bit about the storage of macros: macros are stored in files called modules; modules are stored in folders (or directories) called libraries. Libraries are stored in one of three areas: My Macros - any macros stored in this area will be available to all of your OpenOffice.org documents; OpenOffice.org macros - these are system wide macros available to everyone, however you need admin status to write to this area; The current document - the macro can be stored in an OpenOffice.org document that you've got open; any such macro is available to that document and only that document, but this is useful if you're going to be passing the document (with the macro) on to another user.

Starting the OpenOffice.org Basic IDE

Once you've decided where you're going to be storing your macro then it's time to open the IDE, and to do this simply: start OpenOffice.org and open a new Calc document; click on Tools | Macros | Organize Macros | OpenOffice.org Basic; select the area in which you want to create your macro and click on 'New'; if you're asked for it then enter a new module name (and try to be a bit more inventive than just the default 'Module1').

OpenOffice.org will now place you in the IDE and will create a new blank macro for you (called 'Main') and you can run this macro by pressing the 'Run Macro' button (the button with a green triangle); obviously nothing will happen yet, but that will all change once you start adding code.

Writing Your First Macro

Since OpenOffice.org has kindly provided us with a blank macro, we can take that and make it into something useful; so, let's create a simple macro that will: Write some text to an OpenOffice.org Calc cell; input numbers to an OpenOffice.org Calc cell; add a formula to an OpenOffice.org Calc cell.

Our first, simple macro will look something like:

Sub Main
Dim oSheet
Dim oCell
oSheet = thisComponent.sheets(0)
oCell = oSheet.getCellRangeByName("A1")
oCell.String = Now()
oCell = oSheet.getCellRangeByName("B1")
oCell.Value = 10
oCell = oSheet.getCellRangeByName("B2")
oCell.Value = 32
oCell = oSheet.getCellRangeByName("B3")
oCell.Formula = "=SUM(B1:B2)"
End Sub

You can learn some simple, but key, points from this code: objects and variables are defined using the Dim statement; the thisComponent object represents the current document that you're working with; each sheet in the spreadsheet can be accessed by using the sheets object array; each cell in a sheet can be called by using the getCellRangeByName function; each cell can be written to by setting the Formula, String and Value properties. Now press the 'Run Macro' button (just to remind you, that's the button with a green triangle) and then look at your spreadsheet to see the end result:

A B

1 29/06/2008 13:39:26 10

2 32

3 42

Conclusion

From this simple tutorial you can see how easy it is to start the process of automating OpenOffice.org Calc documents by making use of macros, and, hopefully, you'll soon be able to retire early because of all of that time and effort that you're going to save.