You may, for instance, want to generate data on the command line and include it as a table in a Writer document, or you may obtain information from other sources and need to format it for a weekly report or client newsletter. Each of these tasks would be time-consuming if done manually, but OpenOffice.org Basic can help you minimize the time that you spend doing the more mundane things, and allow you to spend more time on the more interesting parts of your job.
Creating an OpenOffice.org macro
So, how do you start make OpenOffice.org Writer a little more useful? First you need to create a Basic subroutine or function — both are usually refered to as macros.
Go to the Writer menu and click on Tools. You will see a sub-menu called Macros. If you place your mouse over this you’ll be able to see the macro options that are available to you. We’re only interested in Organize Macros at the moment. Select OpenOffice.org Basic… from the next sub-menu. You will be presented with a dialog box that allows you to create a new macro or to edit ones that you’ve already made. By default the application will create a macro called Main. You’ll find yourself in the Macro edit screen, and you should see something like this:
Sub Main End Sub
You’re now ready to create your first macro. We’ll start with the traditional “Hello World!” program.
Sub myFirstMacro print "Hello World!" End Sub
Running a macro
You will, naturally, want to see the end result of your hard work now. Return to the OpenOffice.org Writer screen. Go to the menu and select Tools | Macros again. This time you can either use Organize Macros or Run Macro… to run the macro, or just use the Run Basic button on the toolbar.
There is a second, and much more useful, way of running macros once you’ve tested them. You can assign macros to menus, keyboard shortcuts, toolbar items, and events. To assign a macro, and to see the complete list of possible actions, go back to the Organize Macros window. When you click on Assign, you’ll see all of the possible options.
A more advanced macro
Now let’s look at an OpenOffice.org Basic macro that is of actual use. The next example macro converts a document that uses British English spelling into one that uses American spelling:
Sub toUsaSpelling
Dim searchArray,replaceArray
Dim i as integer
searchArray = Array("colour","flavour","marathon bar")
replaceArray = Array("color","flavor","snickers bar")
for i = 0 to ubound(searchArray)
doReplace(searchArray(i), replaceArray(i))
next i
End Sub
Sub doReplace (searchString as String, replaceString as String)
dim rep as object
rep = ThisComponent.CreateReplaceDescriptor
rep.searchString = searchString
rep.replaceString = replaceString
ThisComponent.replaceAll(rep)
End Sub
If you have used Visual Basic or VBScript on Windows then you should recognize the general format and functionality. If not, a little explanation is probably necessary.
There are two subroutines — toUsaSpelling and doReplace. The subroutine toUsaSpelling creates two arrays, searchArray and replaceArray, containing the British words and their American equivalents. It then steps through the arrays and calls the doReplace subroutine, which takes the two input variables and substitutes one word for the other throughout the OpenOffice.org document.
OpenOffice.org Basic uses all of the normal loops and control structures that you can find in any programming language, along with a number that come directly from Visual Basic. For instance, doReplace uses the ubound function, which returns the highest index number for a array. This function is useful in loops because it means that you don’t have to keep track of the actual size of the array.
Notice that doReplace has a reference to ThisComponent. ThisComponent represents the current OpenOffice.org document.
Next: Adding information from an external file
Placing data in tables can be a time-consuming part of creating a report, but tables make a document easier to read, so it’s worth automating the process. The next macro takes a file and loads it into a table. In this case the table is populated with the username and home directory fields from the /etc/passwd file:
Sub doPasswdTable
Dim filenumber As Integer
Dim lineNumber As Integer
Dim lineCount As Integer
Dim iLine As String
Dim file as String
Dim doc as object
Dim table as object
Dim cursor as object
Dim cellname as object
Dim cell as object
file = "/etc/passwd"
'Get line count
lineCount = 0
filenumber = Freefile
Open file For Input As filenumber
While not EOF(filenumber)
Line Input #filenumber, iLine
If iLine <> "" then
lineCount = lineCount + 1
end if
wend
Close #filenumber
doc = thisComponent
cursor=doc.text.createTextCursor()
table=doc.createInstance("com.sun.star.text.TextTable")
table.initialize(lineCount+1,2)
doc.Text.insertTextContent(cursor,table,False)
cell = table.getCellByName("A1")
cell.string="Username"
cell = table.getCellByName("B1")
cell.string="Home Directory"
filenumber = Freefile
lineNumber = 2
Open file For Input As filenumber
While not EOF(filenumber)
Line Input #filenumber, iLine
If iLine <> "" then
Dim iArray
iArray = split(iLine,":")
cell = table.getCellByName("A" & lineNumber)
cell.string=iArray(0)
cell = table.getCellByName("B" & lineNumber)
cell.string=iArray(5)
lineNumber = lineNumber + 1
end if
wend
Close #filenumber
End Sub
Most of the coding is fairly standard — you can see an example of a while..wend loop, and an if...end if statement. You may also notice the statement doc=thisComponent; this allows me to refer to doc rather than the more cumbersome thisComponent.
Something that may not be as obvious is that Basic is not case-sensitive. Therefore cell = table.getCellByName("A" & lineNumber) will work as well as CELL = table.getcellbyname("A" & LINENUMBER). We use mixed upper- and lower-case letters purely for ease of reading.
The line filenumber = Freefile assigns a unique ID to the filenumber variable, without your having to remember any IDs that you’ve already assigned.
The split command is particularly useful. It takes an string and returns an array of substrings, broken up according to the delimiter that you supply. In the example above we’ve used a colon (:) to identify the field separator in the passwd file.
Finally, one line that may look strange at first is Open file For Input As filenumber. This refers to the fact that we’re using it as an input to the subroutine, not that we’re going to input anything to it.
In conclusion
This is just the briefest of introductions to using Basic in OpenOffice.org Writer. We haven’t covered the use of forms, dealing with other documents, or how to obtain information from datatabases.
If you want to learn more about programming in OpenOffice.org Basic, the OpenOffice.org help comes with a list of all the functions and subroutines that are available. You can also try using the OpenOffice.org Record Macro facility, and then examine the code that the application builds for itself.
OpenOffice.org Basic is a useful tool for automating everyday tasks — just one more way of working smarter instead of harder.
