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