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…
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.
November 23rd, 2007 at 10:30 am
I also needed a “title case” so I extended your script + a script I found in the forums: http://www.oooforum.org/forum/viewtopic.phtml?t=23299
I’ve got limited experience with macros but this seems to work:
Sub TitleC
dim ignore as string
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
ignore=”a,the,than,with,from,of,to,and”
For nRow = oActiveCells.StartRow To oActiveCells.EndRow
For nCol = oActiveCells.StartColumn To oActiveCells.EndColumn
oCell = oSheet.getCellByPosition(nCol,nRow)
CellVal = oCell.getString()
tCaseArr = split(lcase(CellVal,” “))
for q = 0 to ubound(tCaseArr)
if instr(ignore,tCaseArr(q))
January 1st, 2008 at 12:01 pm
If you’re going to post code, you should consider another blogging platform, or changing it so that it doesn’t use smartypants (or whatever its using) to turn all your quotation and speech marks into facy varients that are incorrect syntax for your language