October 7, 2005

An introduction to OpenOffice.org Basic

One of the features some users overlook in OpenOffice.org is its built-in programming language, OpenOffice.org Basic. Why would you want a programming language built into your word processor? It's there to help you to automate tasks. It won't make the tea for you, but it will help you to carry out many repetitive jobs with a minimum of effort.

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
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
	Close #filenumber

	doc = thisComponent

	cell = table.getCellByName("A1")
	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 = table.getCellByName("B" & lineNumber)
			lineNumber = lineNumber + 1
		end if
	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.

Click Here!