First Foray into OpenOffice.org Macro Programming

I have begun working with OpenOffice.org 2.0 macros, and all I have to say is “ouch”, my first experience was not a positive one. I do have a couple of macros that do their intended purpose, but I also have one hell of a headache. I love OpenOffice, it is a great product, but its macro facilities have a long way to go before being considered prime time. The biggest problem is the lack of documentation for any of their objects. While http://api.openoffice.org/ has an API reference, it is cumbersome to find information, forcing you to use the search feature. It is not quite the reference at my fingertips that it should be. This experience has not deterred me from learning how to develop for OpenOffice however, but I do have some major gripes. Thankfully I came across websites of people who have been down this road before, which somewhat got me started in the right direction.

-OpenOffice Basic Macro for Calc (the guy has a few macros for demonstration, he seems to agree about the documentation)
-OpenOffice Tips (probably the first site I found that helped me get a handle on accessing basic things like Cells access)
-Excel VBA to Calc/StarBasic
-OpenOffice API Homepage

My intended goal was to write macros that would do simple tasks first, so the macro I wrote is for Calc and will select a range and delete it. This will work for deleting rows and columns. Below are two different versions, one will simply clear the contents, and the other will delete the range.

Clear the range:
Sub clear_range
     'Generic objects for selecting the current document,
     'worksheet, and storing the range
     Dim oDoc As Object
     Dim oSheet As Object
     Dim oRange As Object

     'Integer for setting the various attributes to clear values
     Dim iCellAttr As Integer

     'Set all attributes for clearing
     iCellAttr = _
          com.sun.star.sheet.CellFlags.VALUE + _
          com.sun.star.sheet.CellFlags.DATETIME + _
          com.sun.star.sheet.CellFlags.STRING + _
          com.sun.star.sheet.CellFlags.ANNOTATION + _
          com.sun.star.sheet.CellFlags.FORMULA + _
          com.sun.star.sheet.CellFlags.HARDATTR + _
          com.sun.star.sheet.CellFlags.STYLES + _
          com.sun.star.sheet.CellFlags.OBJECTS + _
          com.sun.star.sheet.CellFlags.EDITATTR

     'Set the current documents
     oDoc=ThisComponent

     'Get the sheet to clear the range, then select that range and clear
     oSheet=oDoc.Sheets.getByName("Sheet1")
     oRange=oSheet.getCellRangeByName("A1:B7")

     'Clear the range based on the attributes set to clear
     oRange.ClearContents(iCellAttr)
End Sub

Delete the range:
Sub delete_range
     'Generic objects for selecting the current document,
     'worksheet, and storing the range
     Dim oDoc As Object
     Dim oSheet As Object
     Dim oRange As Object

     'Set the current documents
     oDoc=ThisComponent

     'Get the sheet to clear the range, then select that range and clear
     oSheet=oDoc.Sheets.getByName("Sheet1")
     oRange=oSheet.getCellRangeByName("A1:B7")

     ‘Delete the range
     osheet.removeRange(oRange.RangeAddress, com.sun.star.sheet.CellDeleteMode.UP)
End Sub

I could not find a easier way to select rows or columns other than to select a large range of cells, so if you know of one, please share.

If you are setting out to do some work with OpenOffice.org macro facilities, be sure to keep plenty of patience handy, because your going to need it.

Update: I found this very useful document on Andrew Pitonyak website. He is the author of OpenOffice.org Macros Explained. Apparently this document was not up at the time of this writing. But I found tons of useful information in it. Thanks Andrew for posting it.