More OpenOffice.org Macros

While waiting for an excessively long Oracle script to run, I had more time to play around with OpenOffice.org Macros. Have I mentioned how bad the documentation is for OpenOffice? Personally I have very little gripe with OO outside of its incomplete documentation for developers, however some people take their complaints a little further (I agree with a lot of what this guy is saying). I would like to thank Andrew Pitonyak for all his help on the code to select a whole row, and the documentation provided from his site that helped me figure out how to search a document for a value, as well as talking about the undocumented SearchType property used in my example. The below is along the same lines as my previous delete range example, it will search an OpenOffice.org Calc spreadsheet for a matching value and delete the row.

The code is below:
Sub FindAndDeleteRowWithMatch()
     Dim oSheet as object
     dim oRow as object
     dim oCell as object
     Dim oDescriptor as object
     Dim oFound as object


     'Get the first sheet in the active document
     oSheet = ThisComponent.getSheets().getByIndex(0)


     ''Create the search descriptor to searcht eh document
     oDescriptor = oSheet.createSearchDescriptor()


     'Set the text for which to search and other
     'http://api.openoffice.org/docs/common/ref/com/sun/star/util/SearchDescriptor.html
     With oDescriptor
          .SearchString = "100" 'Search for the formula result value of 100
          .SearchWords = True 'Search the whole cell, not just part of it
          .SearchCaseSensitive = False 'Do not be case sensitive
          .SearchType = 1 'Search for values, not just formulas. This is an undocumented property
     End With

     'Get the first result from the search
     oFound = oSheet.findFirst(oDescriptor)

     'If the object is not null or empty, process, otherwise, do not process
     if IsEmpty(oFound) or IsNull(oFound) then
          print "Not Found"
     else
          'Get the row with the result in it
          oRow = oFound.getRows().getByIndex(0)

          'Select the row
          ThisComponent.getCurrentController().select(oRow)

          'Delete the row from the worksheet
          osheet.removeRange(oRow.RangeAddress, com.sun.star.sheet.CellDeleteMode.UP)

          'Inform the user that the row was deleted
          print "Found and Deleted"
     end if
End Sub

This is great, but I decided to extend this just a tad bit more. I wanted to have the same sort of macro as illustrated in my article about the Excel Macro to delete the non-matches from a VLOOKUP. The macro will search the spreadsheet for all values from a VLOOKUP where there is no match and the returned result is “#N/A”. However I ran into an interesting problem. OpenOffice.org does not consider the “#N/A” result from a VLOOKUP a value, so you cannot search for it, nor does it provide a function to search by error codes. This made things a little difficult. Thanks to Andrew Pitonyaks documentation, I was easily able to find a macro that will select a whole sheet as a range, and go through cell by cell to find the matching error code for the “#N/A” value and delete the target row. In the below example I have a simple sheet with a few values, and a VLOOKUP function to compare against the values in another sheet.



Below is the code based off of the macro found in Andrew Pitonyaks documentation (thanks to Gerrit Jasper for providing the original code).

sub FindNAandDelete()
     Dim nCurCol As Integer
     Dim nCurRow As Integer
     Dim nEndCol As Integer
     Dim nEndRow As Integer
     Dim oCell As Object
     Dim oCursor As Object
     Dim aAddress As Variant
     Dim iFind As integer
     dim oSheet as object

     'This is the error value for N/A when a VLOOKUP cannot find a value
     const NA_VALUE as integer = 32767

     'Get the first sheet in the active document
     oSheet = ThisComponent.getSheets().getByIndex(0)

     'Select the range to search
     oCell = oSheet.GetCellbyPosition( 0, 0 )
     oCursor = oSheet.createCursorByRange(oCell)
     oCursor.GotoEndOfUsedArea(True)
     aAddress = oCursor.RangeAddress
     nEndRow = aAddress.EndRow
     nEndCol = aAddress.EndColumn

     For nCurCol = 0 To nEndCol 'Go through the range column by column,
          For nCurRow = 0 To nEndRow 'row by row.
               'Get the current cell, then assign its error value
               oCell = oSheet.GetCellByPosition( nCurCol, nCurRow )
               iFind = oCell.getError()

               'If the value matches the NA Value, then we have a match, select and delete the row
               If iFind = NA_VALUE then
                    oRow = oCell.getRows().getByIndex(0)
                    'Select the row
                    ThisComponent.getCurrentController().select(oRow)

                    'delete the selected row
                    osheet.removeRange(oRow.RangeAddress, com.sun.star.sheet.CellDeleteMode.UP)

                    'Go back 1 row so we do not miss any values
                    nCurRow = nCurRow - 1
               End If
          Next
     Next
End sub

Below is a screenshot of the sheet after the macro was run.



As you can see, it removed all the rows with “#N/A”. While it is not as efficient as I would like, it does do the job. So far, I have found that OO is definitely workable, albeit a little frustrating to work with. Thanks to community support, however, I have not run into an issue yet that I have not been able to work around.