Optimise OpenOffice.org
OpenOffice.org is the greatest open source alternative to Microsoft’s Office suite. Over the years OpenOffice has evolved to become much more than just an alternative to MS Office however. Let’s look at some hacks that will allow you to become more of a power user of OpenOffice, enabling you to be more productive with the software. We’ll explain how to create your own macros and extensions and more besides…
Calc Optimisations
Install templates
OpenOffice has a wonderful collection of templates for Calc, its spreadsheet application. There are calendars, loan payment planning sheets, planners, etc. These templates are smart spreadsheet documents that will help you with a lot of the tasks you would usually use a spreadsheet for. You can download, install and modify these templates. Download templates here, select a Calc template you like and click on the ‘Use It’ button. Once the template file is done downloading, open it with OpenOffice. Now you can work on it, edit it and save it as a modified template if you like.
Conditional formatting for cells
Conditional formatting allows you to format cells differently, based on conditions which you can define. It’s a great way to automatically make some important information stand out. Go to Format>Styles and Formatting. Right-click in the white space inside the window that pops up, and click on New. Now create a new style. Assign it a name, a font and a different background colour. Create another style after you are done creating the first one. Hit OK to save it, and close the Styles and Formatting window. Select the cells where you want conditional formatting to apply. Go to Format >Conditional Formatting. Here you can assign the conditions for the formatting and which style to use for each condition. For example, in the screenshot (left) we have asked Calc to use a particular style if the value in the cell is less than 100, and another if it is greater.
Create a Calc macro
Let’s look at how to create a simple macro for Calc. This macro will place two numbers in cells and then multiply them and put the result in a third cell. Go to Tools>Macros>Organize Macros>OpenOffice.org Basic. In the window that pops up, click on the Organizer button and then click on New to create a new macro. Once you pick the name, hit OK. You will see the OpenOffice.org Basic IDE open. Enter the following code in it, replacing whatever was there. Then Save it and Run the macro. You should see the number 37 and 59 appear in cells A2 and A3, and their multiplied total in the cell A4.
Sub Main
Dim oSheet
oSheet = thisComponent.Sheets(0)
oSheet.getCellRangeByName(“A1”).String = “Simple Math”
oSheet.getCellRangeByName(“A2”).Value = 37
oSheet.getCellRangeByName(“A3”).Value = 59
oSheet.getCellRangeByName(“A4”).Formula = “=A2*A3”
End Sub
Impress Optimisations
Create a photo album
The Photo Album extension for OpenOffice.org Impress is a very useful plug-in. It allows you to create a photo album presentation with a few clicks of the mouse, within a few seconds. You can download it here. Click on the ‘Get It’ link on the page to begin the download. To install, open the download zip file with Tools>Extension Manager. To launch the Photo Album extension, go to Tools>Add-ons>Create Photo Album. Now direct it to the pictures you want to use in your photo album. A couple of clicks later, you should have a ready-to-use presentation on your hands. You can tweak the transitions and change other things if you like.
Import Microsoft Office templates
MS Office ships with the Powerpoint presentation tool. Due to its popularity, there are several free (as well as commercial) templates available for it. If you have MS Powerpoint installed on a computer, you can export the templates from it so that they can be imported into Impress. First, you need to convert the templates to the OpenOffice format. Go to File>Wizards>Document Converter. Follow the wizard as it runs you through the process of converting the templates.
Go to Tools>Options>OpenOffice.org>Paths and click on Edit. Click on Add and add the path of the converted templates you want to add to OpenOffice. Now when you click on File>New>Templates and Documents, you should see your new templates available for use.
OpenOffice.org IDE for creating and editing macros
OpenOffice.org allows you to create macros by either recording your actions or by writing Basic code.
You can create OpenOffice macros by recording your actions, as we saw earlier. You can also use the Basic IDE to write the macros by hand. When you record a macro, you can also open up the macro in the IDE and edit it to add more features, or just tweak it. A good way to get started with creating macros using the IDE is to either record a macro and edit it, or to edit the code of one of the macros that ships with OpenOffice. Go to Tools>Macros >Organize Macros>OpenOffice.org Basic. In the window that pops up, select the macro you want to edit and click on the Edit button. The Basic IDE will pop up with the code of the chosen macro. There are a number of resources online where you can find samples of macro code – for example, here. The IDE is pretty helpful in that it allows you to watch variables and set up and monitor break points as you develop and test your macro code.
This article originally appeared in issue 84 of Linux User & Developer magazine.
Linux User & Developer, one of the nation’s favourite Linux and Open Source publications, is now part of the award winning Imagine Publishing family. Readers can subscribe and save more than 30% and receive our exclusive money back guarantee – click here to find out more.
to find out about all the latest Linux news, reviews, previews, interviews, features and a whole more.
You missed the biggest tip of all: How to properly print to an envelope. I have the vanilla-ist of printers; an HP Laserjet 4. I have looked at maybe a dozen web articles telling me how to print envelopes in Openoffice and none of them worked. Printing in Slackware 7.1 to my Deskjet 520 worked better than this. Printing to envelopes; now there’s a story for you.
i have opened 10 tickets with open office so far since it was star office and it cannot do basic new line search and replace.
10 times my ticket was closed in 10 years telling me its not important feature that needs attention
Find, More options, Regular expressions does it, although you do have to know regular expressions. OTOH, how would you search for new lines in MS Word?
BTW, in keeping with the spirit of the article: I would take a text file which has new lines between lines, and two new lines between paragraphs, and record a macro that would have new lines only between paragraphs. Take a README file, for instance. Anyway, first thing to *before* recording is check the Edit > Find dialog to see if it does Regular Expressions. Depending on your preferences, you may leave that box unchecked first, and let the macro take care of checking it upon start, then unchecking it when it exits. Or, if you keep it checked, you’ll record the macro from the time you do the Edit > Find.
Enter \n\n in the “Search for: box, and ~!~ in the “Replace with” box, then click on the Replace All button. That replaces all the double line feeds with the string “~!~”. I’m doing this because I’ll replace single occurrences of \n with a space. If my first find-and-replace was for the single occurrences of \n, paragraphs would run into each other.
The Search-and-replace dialog box stays on-screen. Now, Enter ~!~ in the “Search for” box, and \n\n in the “Replace with” box, then click on the Replace All button. That one breaks paragraphs with two line feeds.
If you prefer to keep Regular Expressions disabled, uncheck the relevant check box. Close the Find-Replace dialog box, stop the macro recording.
One thing I haven’t checked is the effect of paragraph styles on the macro. Like, in my default style sheets, I have two line feed between paragraphs. If that’s also the case for your default style sheet, your replacement string for ~!~ will be a single \n.
As to the ~!~ thingy, I chose that on the assumption that text normally wouldn’t contain that character. Might be safer to use a longer string, e.g., ThisStringIsTemporaryParagraphBreak — since this is a macro, you’ll be doing that only once anyway :)
Sorry, forgot an important step. After doing the search-and-replace of \n\n with ~!~, next enter \n in the “Search for” box, and a single space in the “Replace with” box, then Replace All. After this, Search for: ~!~, then Replace with: \n\n, etc…
Update: my style sheet automagically inserts two line feeds between paragraphs, so when I do the last step of the macro-to-be , I only need to replace the ~!~ with a single \n. Of course, I recommend a style sheet if you consistently want two line feeds between paragraphs, and to save it as the default style.