OpenOffice Macros: Accessing Calc Cells

How to Write To and Read From an OpenOffice.org Calc Spreadsheet

Aug 13, 2008 Mark Alexander Bain

This tutorial shows how to do one of the most important things that an OpenOffice Calc macro must do - reading from (and writing to) cells.

If anyone writes a Basic Macro for OpenOffice.org Calc then they must be able to access cells in the spreadsheet, and they must be able to write to and read from those cells. Developers moving form Microsoft Excel to OpenOffice.org Calc may well already be used to the Cells object; unfortunately OpenOffice.org does not have this and cell access is done in a rather different way - not difficult, just different.

Accessing the OpenOffice.org Calc Spreadsheet

Before accessing a Calc cell the macro must access the Calc sheet, either by calling the sheet by name:

Sheet = thisComponent.Sheets("Sheet1")

or by number:

Sheet = thisComponent.Sheets(0)

In both cases the thisComponent object is used, and this is, of course, the spreadsheet from which the macro is being run.

Once the sheet itself has been selected then the macro can move on to accessing the cells themselves, and this can be done in either of two ways:

  • by name
  • by position

Accessing OpenOffice Calc Cells by Name

OpenOffice Calc cells are all labelled (A1, B2, Z100, etc) and one way of accessing the cells from a macro uses these labels - the getCellRangeByName method:

Cell = Sheet.getCellRangeByName("A1")

This method is the most "human friendly" way of accessing a cell (since it reflects the way in which the cells are labelled); however, it's often easier to write macros that access the cells by using they position rather than the label.

Accessing OpenOffice Calc Cells by Position

Rather than using the getCellRangeByName method to access a cell by its label, it is possible to access a cell by its position by using the getCellByPosition method; for instance:

c = 0

r = 0

Cell = Sheet.getCellByPosition(c, r)

This time, rather than using the cell's name (e.g. "A1") its column number and row number needs to be passed to the method.

Types of Data in an OpenOffice Calc Spreadsheet

Once it's been accessed the OpenOffice Calc spreadsheet cell will contain one of three data types:

  • Formula - an OpenOffice Calc formula such as "=A1+A2" or "=SUM(B2:B25)"
  • String - a text string such as "How to access a cell"
  • Value - a number such as 1, 10, 99.9

This data type is critical because it affects how the cell handles its contents, for example:

  • if "=1+1" is entered as text and not as a formula then the result will be "=1+1" and not 2
  • if "3" is entered as text then it's numerical value will be 0

Using an OpenOffice Calc Cell's Contents

Having selected the cell and decided which data type to use then the macro can either read information from the cell:

myString = Cell.String

or the cells can be written to (and the getCellByPosition method can be particularly useful when used in a loop):

Sub writeToCells

Dim Sheet, Cell

Dim c as Integer, r as Integer

Sheet = thisComponent.Sheets(0)

Cell = Sheet.getCellRangeByName("A1")

Cell.String = "Values"

c = 0

for r = 1 to 10

Cell = Sheet.getCellByPosition(c, r)

Cell.Value = r

next r

Cell = Sheet.getCellRangeByName("B1")

Cell.String = "Totals"

Cell = Sheet.getCellRangeByName("B2")

Cell.Fornula = "=SUM(A2:A11)"

End Sub

In this example the code:

  • writes text to cells A1 and B1
  • writes numbers into cells A2 - A11
  • write a formula into cell B2

Conclusion

OpenOffice's methods for accessing Calc spreadsheet cells are slightly more involved than Excel's VBA, but is by no means difficult - it's just a matter of using one of the document's methods:

  • getCellRangeByName
  • getCellByPosition

and then calling the particular data type required:

  • Formula
  • String
  • Value

With those a user can automate whatever they like within the Calc Spreadsheet.

References

Mark Alexander Bain, Learn OpenOffice.org Spreadsheet Macro Programming: OOoBasic and Calc automation (Packt Publishing, 2006)

The copyright of the article OpenOffice Macros: Accessing Calc Cells in Computer Software is owned by Mark Alexander Bain. Permission to republish OpenOffice Macros: Accessing Calc Cells in print or online must be granted by the author in writing.
What do you think about this article?
NOTE: Because you are not a Suite101 member, your comment will be moderated before it is viewable.
post your comment
What is 8+8?

Comments

Sep 17, 2008 8:00 AM
Guest :
Your articles are simple and helps me for the migration from MS office to Open office suite. I used to write macro applications with VBA in MS office.
Sep 20, 2008 4:30 AM
Guest :
Great, your article helped me a lot!
Thx!
Oct 14, 2008 10:06 AM
Guest :
You're a ****ing magician when it comes to teaching stupid monkeys like me, thanks a lot.
Nov 19, 2008 11:38 PM
Guest :
hey can anyone please tell me how to access the cells that are selected. its like i want to access the cells dynamically not specifically. I dont know where the user will put in the data ( as in which cells) Actually i have a program that converts numbers in figures to numbers in words.
So i need to access the cells that the user has selected and then display the result in the adjacent cell..
Please can anyone help me!..
Thanks a ton!!
Radhika
Mar 10, 2009 3:04 PM
Guest :
Thank you so much for a very simple start to this stuff. Just what I needed to get going!!!
Aug 30, 2009 8:32 AM
Guest :
Thanks. I couldn't find this information anywhere.

just so you know, in the 2nd to last line of your example macro, it says "cell.fornula" rather than "cell.formula".
6 Comments