« Summing across sheets | Main | Filling a multiplication chart »
August 29, 2005
Macros: Text Utilities Part I
It is straightforward to enhance the capabilities of OOo Calc with your home grown functions and features. In this tip, we add the ability to change the case of the text in a range of selected cells.(UPPER CASE, lower case or Proper Case).As you can in the screenshot below, we have added a new top level menu item TextUtils with 3 new functions, UpperC, LowerC, and ProperC. The last function capitalizes the first character only.
We could have invoked these functions with Tools - Macros etc but from a user's standpoint, it is much more convenient to invoke them the way we have implemented it.
Pretty straightforward - the results of applying UpperC to the selected cells.
First - the source code...
Sub UpperC
oDesktop = createUnoService("com.sun.star.frame.Desktop")
oDocument = ThisComponent
oSelectedCells = oDocument.CurrentSelection
oActiveCells = oSelectedCells.RangeAddress
oSheets = oDocument.Sheets
oSheet = oSheets.getByIndex(oActiveCells.Sheet) ' active table
For nRow = oActiveCells.StartRow To oActiveCells.EndRow
For nCol = oActiveCells.StartColumn To oActiveCells.EndColumn
oCell = oSheet.getCellByPosition(nCol,nRow)
CellVal = oCell.getString()
oCell.setString(UCase(CellVal))
Next
Next
End Sub
Sub LowerC
oDesktop = createUnoService("com.sun.star.frame.Desktop")
oDocument = ThisComponent
oSelectedCells = oDocument.CurrentSelection
oActiveCells = oSelectedCells.RangeAddress
oSheets = oDocument.Sheets
oSheet = oSheets.getByIndex(oActiveCells.Sheet) ' active table
For nRow = oActiveCells.StartRow To oActiveCells.EndRow
For nCol = oActiveCells.StartColumn To oActiveCells.EndColumn
oCell = oSheet.getCellByPosition(nCol,nRow)
CellVal = oCell.getString()
oCell.setString(LCase(CellVal))
Next
Next
End Sub
Sub ProperC
oDesktop = createUnoService("com.sun.star.frame.Desktop")
oDocument = ThisComponent
oSelectedCells = oDocument.CurrentSelection
oActiveCells = oSelectedCells.RangeAddress
oSheets = oDocument.Sheets
oSheet = oSheets.getByIndex(oActiveCells.Sheet) ' active table
For nRow = oActiveCells.StartRow To oActiveCells.EndRow
For nCol = oActiveCells.StartColumn To oActiveCells.EndColumn
oCell = oSheet.getCellByPosition(nCol,nRow)
CellVal = oCell.getString()
oCell.setString(UCase(Left(CellVal,1)) & Right(CellVal,Len(CellVal) - 1) )
Next
Next
End Sub
Creating the new menu entries is simple. Invoke the Menu Customization dialog with Tools - Customize as shown below...
Select the Menus tab. Add a new top level menu item - TextUtils and then a seperate entry for each of our new functions.
Here is the Add Commands dialog. We select the module in the left hand box and the macro subroutine on the right. By default, the menu entry is given the same name as the subroutine but hat can be changed.
In the next installment, we will add a dialog to enhance out new functionality even further.
Posted by Dave at August 29, 2005 09:50 PM