Creating a macro

Getting Started with OpenOffice.org 3

Chapter 13. Getting Started with Macros

< Previous Section

Next Section >

• Creating a simple macro
• Creating a macro
• Sometimes the macro recorder fails
• Macro organization
• How to run a macro
• Extensions
• Writing macros without the recorder
• Finding more information

< Previous Page

Next Page >

A recorded macro repeats the same task over and over again. Before creating a recorded macro, I usually ask two questions:

1. Can the task be summarized as a simple set of commands that do not change?
2. Can the steps be arranged such that the last command leaves the cursor ready for the next command?

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.

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.

Running the macro quickly

It is tedious to repeatedly run the macro using Tools > Macros > Run Macro. The macro can be run from the IDE. Use Tools > Macros > Organize Macros > OpenOffice.org Basic to open the Basic Macro dialog. Select your macro and click Edit to open the macro in the IDE.

The IDE has a Run Basic icon in the toolbar that runs the first macro in the IDE. Unless you change the first macro, it is the empty macro named Main. Modify Main so that it reads as shown in Listing 3.

Listing 3: Modify Main to call CopyNumToCol1.

 Sub Main
   CopyNumToCol1
 End Sub

Now, you can run CopyNumToCol1 by repeatedly clicking the Run Basic icon in the toolbar of the IDE. This is very fast and easy, especially for temporary macros that will be used a few times and then discarded.