My third experience with OpenOffice V3 Macros

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, things 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
Advertisements

5 Responses to “My third experience with OpenOffice V3 Macros”

  1. VBA support in OpenOffice.Org Calc | Learning Daily Says:

    […] from the command line. I was convinced that VBA and OOoCalc are not compatible, till I discovered this site by accident when trying to learn to write Javascript Macros in […]

  2. Edsko de Vries Says:

    Hi,

    Thanks for an interesting blog post! Unfortunately, when I try your VBA solution in OpenOffice, it does not seem to work. Even with the “Option VBASupport 1”, I still get the “Object variable not set” error in the first If. It’s very frustrating :-/ Any ideas? Any chance you could send me an OpenOffice document that shows your code (CompleteCategoryRowVBA) working?

  3. amagard Says:

    Hi Edsko,
    you can download my sample openoffice file from here. There is probably an error in the code I posted: it should be “Selection” rather than “selection” to address this object properly. I will fix the posting now.

  4. Locusi Says:

    Same here. “Object variable not set” all the time. And also I don’t see any macro in your sample OO file (I must copy->paste macro from here) but thanks for the effort anyway 🙂

  5. 2010 in review « Axel’s Travelog Says:

    […] My third experience with OpenOffice V3 Macros March 2009 4 comments Posted in blogging. Leave a Comment » LikeBe the first to like this post. […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: