After my first expereince with OpenOffice V3 Macro has been a complete failure I investigated a little further and made a second experience: here I learned that the sample macros provided do work – but only for text documents, not for spreadsheets.
I still have been working on trying to get one of my simple sample MS Excel macros to work with OpenOffice V3. Both applications – and if we include Lotus Symphony there would be three – support BASIC as a language to write macros. The challenge lies in the different object models used by these applications how to access services and how to access documents and parts of a document like sheets or cells in a spreadsheet.
Let me explain first what my MS Excel macro is supposed to do. Basically it is supposed to convert something like this shown here on the left side to something like this shown here on the right side:
Imagine you receive a spreadsheet in which some columns are used for category or grouping values and to make it better readable the value is only present when it changes. This is nice unless you want to sort this spreadsheet by the first column which would create an incorrect output or unless you like to use that spreadsheet as an input for a database import which would cause problems due to missing values in the first column.
Here is how I solved this in MS Excel by writing this VBA macro:
1: Sub CompleteCategoryRow()
2: Dim c As Long
3: Dim r As Long
4: Dim contents As String
5: If Selection.Rows.Count <= 1 Then
6: MsgBox "Nothing selected.", vbOKOnly
7: End If
8: contents = " "
9: For c = 1 To Selection.Columns.Count
10: For r = 1 To Selection.Rows.Count
11: If Selection.Cells(r, c) <> "" And Selection.Cells(r, c) <> " " Then
12: contents = Selection.Cells(r, c)
13: Else
14: Selection.Cells(r, c) = contents
15: End If
16: Next r
17: Next c
18: End Sub
Little side comment: to still get a better readability we could add the following line right after the Else statement’: Selection.Cells(r, c).Font.Color = RGB(255, 255, 255). This would still add the contents but display it with white color. So as long as your background color is white you wouldn’t see it.
Trying to run my MS Excel macro in OpenOffice V3 simply would generate an error as shown below:
Since there is no easy way to convert those macros from MS Excel to OpenOffice I noticed this subject is going to become more complicated and I started to use a different approach in solving the problem: reading. Reading books is supposed to make you smart, isn’t it ? Somewhere I found this book being recommended: “Learn OpenOffice.org Spreadsheet Macro Programming: OOoBasic and Calc Automation: A Fast and Friendly Tutorial to Writing Macros and Spreadsheet Applications” by Mark Alexander Bain.
Chapter 1 on the book describes how to work with the IDE of OpenOffice, chapter 2 how macros are organized – in libraries, modules, sub routines and functions – and chapter 3 is about the OpenOffce Object Model. I found this chapter quiet hard to digest. It introduces UNOs—OpenOffice.org’s Universal Network Objects —and then it is about clients, interfaces, methods and services, thinks I actually did not really want to read about before being enabled to come up with my first OpenOffice macro.
In Chapter 4 it is getting more practical and the first working macro is shown in sub chapter “Manipulating Spreadsheet Cells” – not a “Hello World” one, but one which opens up a blank spreadsheet and displays current date and time in the first cell of this sheet.
The follow-on chapters deal with subjects I might be interested in at a later time, like formatting sheets, working with databases, working with other documents, developing dialogs and creating a complete application. Chapter 10 sounds interesting and is about Using Excel VBA in OpenOffice, something for me to read in more detail soon. It mentions that there are plans that a future version of OpenOffice ( written after the book has been published, so may be this is V 3 already ? ) might contain VBA support.
The sample macro from chapter 4 was a good start for me but did not resolve major questions I have had at that point, like especially
- how to work on the actual spreadsheet already opened,
- and how to access a range in that spreadsheet currently selected.
The book also contains a link to the OpenOffice API documentation site and some hints how to use that. I googled through dozen of pages in the internet to find a way how to access a selected range in the current active sheet. I finally learned about the getCurrenSelection function in the XModel interface, but it turned out that this API documentation is insufficient: no examples provided how to use it, even no details about what type of object this function would return.
After more googling I was almost ready to give up for now until I got the idea to do what I sometimes do when there seems to be no information available about a particular program: I debug it. The need to start debugging is an indicator that knowledge management has failed so far in providing to you the knowledge you need. Thus: time to find out on your own and may be contributing to the incomplete body of knowledge out there in this particular realm.
The OpenOffice IDE allows to run a macro step by step and to add watch points. Using that feature I explored the object returned by the getCurrenSelection function and saw that it provides an attribute DataArray giving access to all the cells in a selected range.
With that previously missing piece of knowledge discovered through code debugging I was able to finally come up with this OpenOffice V3 macro BASIC code supposed to do exactly the same thing than my MS Excel macro:
1: Sub CompleteCategoryRow
2: xSelection = thisComponent.getCurrentSelection()
3: If xselection.Rows.Count <= 1 Then
4: MsgBox "Nothing selected."
5: End If
6: contents = " "
7: Data() = xselection.getDataArray()
8: For c = 1 To xselection.Columns.Count
9: For r = 1 To xselection.Rows.Count
10: If Data(r-1)(c-1) <> "" And Data(r-1)(c-1) <> " " Then
11: contents = Data(r-1)(c-1)
12: Else
13: Data(r-1)(c-1) = contents
14: End If
15: Next r
16: Next c
17: xselection.setDataArray(Data())
18: End Sub
And, Eureka!, it works ! My first OpenOffice V3 Spreadsheet macro is running, as you can see in this screen cast!
Update:
Pure VBA code works as well in OpenOffice V 3 ! Simply adding “Option VBASupport 1” to the original MS Excel VBA code does the trick, as I learned from chapter 10 of the book I mentioned above. Thus: OpenOffice V 3 includes Microsoft VBA support ! The code shown below works as well and does exactly the same thing than the code I showed above:
1: Option VBASupport 1
2:
3: Sub CompleteCategoryRowVBA
4: Dim c As Long
5: Dim r As Long
6: Dim contents As String
7: If selection.Rows.Count <= 1 Then
8: MsgBox "Nothing selected.", vbOKOnly
9: End If
10: contents = " "
11: For c = 1 To selection.Columns.Count
12: For r = 1 To selection.Rows.Count
13: If selection.Cells(r, c) <> "" And selection.Cells(r, c) <> " " Then
14: contents = selection.Cells(r, c)
15: Else
16: selection.Cells(r, c) = contents
17: End If
18: Next r
19: Next c
20: End Sub
. 
