| |
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.
| DONTKNOW | The font weight is not specified/known.
|
| THIN | specifies a 50% font weight.
|
| ULTRALIGHT | specifies a 60% font weight.
|
| LIGHT | specifies a 75% font weight.
|
| SEMILIGHT | specifies a 90% font weight.
|
| NORMAL | specifies a normal font weight.
|
| SEMIBOLD | specifies a 110% font weight.
|
| BOLD | specifies a 150% font weight.
|
| ULTRABOLD | specifies a 175% font weight.
|
| BLACK | specifies 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.
- Use Tools > Macros > Record Macro to start recording.
- Press Ctrl+Right Arrow to move the cursor to the start of “specifies".
- Press Backspace twice to remove the tab and the space.
- Press Tab to add the tab without the space after the constant name.
- Press Delete to delete the lower case s and then press S to add an upper case S.
- Press Ctrl+Right Arrow twice to move the cursor to the start of the number.
- Press Ctrl+Shift+Right Arrow to select and move the cursor before the % sign.
- Press Ctrl+C to copy the selected text to the clipboard.
- Press End to move the cursor to the end of the line.
- Press Backspace twice to remove the two trailing spaces.
- Press Home to move the cursor to the start of the line.
- Press Ctrl+V to paste the selected number to the start of the line.
- Pasting the value also pasted an extra space, so press Backspace to remove the extra space.
- Press Tab to insert a tab between the number and the name.
- Press Home to move to the start of the line.
- Press down arrow to move to the next line.
- 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.
|
|
