Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

OpenOffice 3.x Getting Started Guide
Previous Page Home Next Page

A complicated example

I frequently copy rows and columns of data from a web site and format them as a table in a text document. First, I copy the table from the web site to the clipboard. To avoid strange formatting and fonts, I paste the text into a Writer document as unformatted text. I reformat the text with tabs between columns so that I can use Table > Convert > Text to Table to convert to a table.

I inspect the text to see if I can record a macro to format the text (remember the two questions that I ask). As an example, I copied the FontWeight constants group from the OpenOffice.org web site. The first column indicates the constant name. Each name is followed by a space and a tab.

DONTKNOWThe font weight is not specified/known.
THINspecifies a 50% font weight.
ULTRALIGHT   specifies a 60% font weight.
LIGHTspecifies a 75% font weight.
SEMILIGHTspecifies a 90% font weight.
NORMALspecifies a normal font weight.
SEMIBOLDspecifies a 110% font weight.
BOLDspecifies a 150% font weight.
ULTRABOLDspecifies a 175% font weight.
BLACKspecifies a 200% font weight.

I want the first column to contain the numeric value, the second column the name, and the third column the description. The desired work is easily accomplished for every row except for DONTKNOW and NORMAL, which do not contain a numeric value—but I know that the values are 0 and 100, so I will enter those manually.

The data can be cleaned in multiple ways—all of them easy. The first example uses keystrokes that assume the cursor is at the start of the line with the text THIN.

  1. Use Tools > Macros > Record Macro to start recording.
  2. Press Ctrl+Right Arrow to move the cursor to the start of “specifies".
  3. Press Backspace twice to remove the tab and the space.
  4. Press Tab to add the tab without the space after the constant name.
  5. Press Delete to delete the lower case s and then press S to add an upper case S.
  6. Press Ctrl+Right Arrow twice to move the cursor to the start of the number.
  7. Press Ctrl+Shift+Right Arrow to select and move the cursor before the % sign.
  8. Press Ctrl+C to copy the selected text to the clipboard.
  9. Press End to move the cursor to the end of the line.
  10. Press Backspace twice to remove the two trailing spaces.
  11. Press Home to move the cursor to the start of the line.
  12. Press Ctrl+V to paste the selected number to the start of the line.
  13. Pasting the value also pasted an extra space, so press Backspace to remove the extra space.
  14. Press Tab to insert a tab between the number and the name.
  15. Press Home to move to the start of the line.
  16. Press down arrow to move to the next line.
  17. Stop recording the macro and save the macro.

It takes much longer to read and write the steps than to record the macro. Work slowly and think about the steps as you do them. With practice this becomes second nature.

The generated macro has been modified to contain the step number in the comments to match the code to the step above.

Listing 2: Copy the numeric value to the start of the column.

 sub CopyNumToCol1
 rem ----------------------------------------------------------------------
 rem define variables
 dim document   as object
 dim dispatcher as object
 rem ----------------------------------------------------------------------
 rem get access to the document
 document   = ThisComponent.CurrentController.Frame
 dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
 
 rem (2) Press Ctrl+Right Arrow to move the cursor to the start of "specifies".
 dispatcher.executeDispatch(document, ".uno:GoToNextWord", "", 0, Array())
 
 rem (3) Press Backspace twice to remove the tab and the space.
 dispatcher.executeDispatch(document, ".uno:SwBackspace", "", 0, Array())
 
 rem ----------------------------------------------------------------------
 dispatcher.executeDispatch(document, ".uno:SwBackspace", "", 0, Array())
 
 rem (4) Press Tab to add the tab without the space after the constant name.
 dim args4(0) as new com.sun.star.beans.PropertyValue
 args4(0).Name = "Text"
 args4(0).Value = CHR$(9)
 
 dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, args4())
 
 rem (5) Press Delete to delete the lower case s ....
 dispatcher.executeDispatch(document, ".uno:Delete", "", 0, Array())
 
 rem (5) ... and then press S to add an upper case S.
 dim args6(0) as new com.sun.star.beans.PropertyValue
 args6(0).Name = "Text"
 args6(0).Value = "S"
 
 dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, args6())
 
 rem (6) Press Ctrl+Right Arrow twice to move the cursor to the number.
 dispatcher.executeDispatch(document, ".uno:GoToNextWord", "", 0, Array())
 
 rem ----------------------------------------------------------------------
 dispatcher.executeDispatch(document, ".uno:GoToNextWord", "", 0, Array())
 
 rem (7) Press Ctrl+Shift+Right Arrow to select the number.
 dispatcher.executeDispatch(document, ".uno:WordRightSel", "", 0, Array())
 
 rem (8) Press Ctrl+C to copy the selected text to the clipboard.
 dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
 
 rem (9) Press End to move the cursor to the end of the line.
 dispatcher.executeDispatch(document, ".uno:GoToEndOfLine", "", 0, Array())
 
 rem (10) Press Backspace twice to remove the two trailing spaces.
 dispatcher.executeDispatch(document, ".uno:SwBackspace", "", 0, Array())
 
 rem ----------------------------------------------------------------------
 dispatcher.executeDispatch(document, ".uno:SwBackspace", "", 0, Array())
 
 rem (11) Press Home to move the cursor to the start of the line.
 dispatcher.executeDispatch(document, ".uno:GoToStartOfLine", "", 0, Array())
 
 rem (12) Press Ctrl+V to paste the selected number to the start of the line.
 dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
 
 rem (13) Press Backspace to remove the extra space.
 dispatcher.executeDispatch(document, ".uno:SwBackspace", "", 0, Array())
 
 rem (14) Press Tab to insert a tab between the number and the name.
 dim args17(0) as new com.sun.star.beans.PropertyValue
 args17(0).Name = "Text"
 args17(0).Value = CHR$(9)
 
 dispatcher.executeDispatch(document, ".uno:InsertText", "", 0, args17())
 
 rem (15) Press Home to move to the start of the line.
 dispatcher.executeDispatch(document, ".uno:GoToStartOfLine", "", 0, Array())
 
 rem (16) Press down arrow to move to the next line.
 dim args19(1) as new com.sun.star.beans.PropertyValue
 args19(0).Name = "Count"
 args19(0).Value = 1
 args19(1).Name = "Select"
 args19(1).Value = false
 
 dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args19())
 end sub

Cursor movements are used for all operations (as opposed to searching). If run on the DONTKNOW line, the word weight is moved to the front of the line, and the first “The" is changed to “She". This is not perfect, but I should not have run the macro on the lines that did not have the proper format; I need to do these manually.


OpenOffice 3.x Getting Started Guide
Previous Page Home Next Page

 
 
  Published under the terms of the Creative Commons License Design by Interspire