OpenOffice / StarOffice macros

So you've taken the plunge and installed StarOffice or OpenOffice and everything basically works; so far so good.

Now Bob from Accounts wants to automatically send summaries of last month's sales to each of the department heads from his Calc spreadsheet. Even more alarmingly he thinks you can just do this with a few lines of code or a recorded macro because he's sure he read something about it on a web site somewhere....

You can:

  • Tell Bob it can't be done.
    Bob now tells his best mate the CEO that this new system you said we should use doesn't work. Not good.
  • Show Bob the macro editor and recorder and let him try for himself.
    You are entering a world of hurt - Bob isn't a programmer and, despite what certain large software companies suggest, it takes programming skills to write programs (duh!). Bob will be back real soon now with lots of questions that start 'How do I...'. This is starting to look like option (a).
  • Try to do it yourself in your copious free time.
    You get points for being helpful but have to give them back when the rest of your work starts to suffer (you do have other things to do don't you?). And you know deep in your heart of hearts that you're not a programmer either - option (a) again.
  • Get someone who has done it before to help.
    Now why didn't you think of that earlier?

Why develop OpenOffice macros at all?

For the same reasons you once wanted to develop MS Office macros.

Using OpenOffice macros

For the end user, using a file which has macros should be much the same in either OpenOffice or MS Office. In both cases, well-designed applications will conceal the complications behind a single command button or menu item.

The OpenOffice/StarOffice macro programming environment offers a similar level of functionality to the MS Office 'VBA' environment, however it is much more programmer oriented and less friendly to end-users.

Converting MS Office files

Unless you happen to have a completely new environment you probably have a whole bunch of existing MS Office format files which you will need to use with OpenOffice. For many of these files the transition will be seamless - you can just open the file with OpenOffice, edit, save and share with MS Office users without worrying. Sometimes you won't be so lucky.

Before you decide to implement OpenOffice you should analyse a representative set of your existing document for compatibility with OpenOffice. The following table shows some of document content elements and how well they might convert. However, the difficulty or otherwise with which a given type of file will interoperate can vary greatly depending on the details of how it has been created in the first place.

Things that convert fairly easily:

  • Document construction templates
  • Formulae
  • Tables and charts

Things that don't convert so easily:

  • Graphs and charts
  • Frames
  • Slide transitions
  • Forms

These more problematic items can be converted and supported in OpenOffice, it's just that they don't convert so well. Of course this isn't a complete list.

Macro Conversion

Converting, or preferably re-engineering existing macros from Word and Excel should be part of your OpenOffice deployment project and scheduled well before end-users are migrated.

The next release of the commercial StarOffice product (version 8) includes an MS Office macro interpreter which, according to the specifications will run existing MS Office macros without conversion. There is some anecdotal evidence from beta test sites that it works well. We shall see...

MS Office development

For many organisations the MS Office suite remains an effective solution for tactical applications. Unfortunately, Bob used to work here and created a myriad of macro-driven spreadsheets and databases that are now essential to your business.

Except something doesn't work and no-one knows how to fix it.

Although the macro programming environment in the MS suite is more user-friendly, it is still handing out machine-guns to babies. Simple automation tasks can be safely recorded by end-users without too much trouble, but once you start to edit the VBA code directly you should have a long hard look at yourself and ask 'Am I the right person to be doing this?' - the answer is probably 'No'.

Why develop MS Office macros at all?

The functionality delivered off-the-shelf by both MS Office and OpenOffice already includes many functions the most users will never require, so why would anyone want to add yet more features? The answer is that adding some fairly simple, but necessarily customised functions, can make many daily tasks much more efficient.

For instance:

  • Word processing documents You can automate the 'fill in the blanks' part of creating letters, faxes, memos etc and (a) make the process much quicker and (b) be sure that all your correspondence carries the same organisational branding.
  • Spreadsheets Reporting and charting applications, perhaps extracting data from a central database for more detailed manipulation.
  • Database applications Could be anything from a custom order entry procedure to an analysis and reporting application supporting an existing third-part application.

There are just some of the processed that can benefit from macro automation. In general, any process where someone spends a large proportion of their time producing many similar results might benefit from automation.

But there's this really good wizard thingy! Can't I use that and do it myself?

Sure, you can give it a shot, and if what you want is close to what the wizards think you want then you might even get a usable result. Sadly, once the wizards prove insufficient you need to know what you're doing.

An analogy might be illuminating: I know enough about car engines to check fluid levels and cables and can nod at the right times when speaking to mechanics, but I'm not about to try replacing a cylinder head myself.


LAMP is a semi-standard platform for developing web-based applications using Linux (or Windows), Apache, MySQL (or PostgreSQL) and Perl (or PHP or Python).

LAMP is growing increasingly important as more applications are developed under this framework. Many open source web-based applications are based on the LAMP environment.


Linux or Windows The operating system layer.
Although many open source applications have a Linux background, those which use are based on the LAMP environment will generally run little or no modification under MS Windows.
Apache The web server layer.
Apache is the most widely used web server on the internet. As well as delivering static HTML pages, a web server can also support dynamic content technologies such as scripting engines.
MySQL or PostgreSQL The database layer.
MySQL and PostgreSQL both have their adherents; I tend to think there is little to choose between them since MySQL added the InnoDB table type which supports transactions.
Perl, PHP, Python The scripting layer.
All of these languages are fully-fledged programming environments in themselves, but can, with the proper configurations, be embedded into web pages to provide the business logic required by various applications.

Aren't web-based applications really slow?


Badly written applications, or those run in misconfigured or inadequate environments are slow. One of the great attractions of the LAMP environment is the ability of the Apache server to execute scripting languages very quickly.

If you have adequate server and network resources, the execution speed of the language isn't generally an issue.

Web Sites

I want to be at the top of Google's search results.

So does everyone else. Google (and other search engines) use sophisticated ranking algorithms designed to reduce the ability of web designers to influence a sites position in search results, so there is only so much that you can do.

My tips for helping search engines:

  • Have some content worth finding. If you're at the top of the search rankings but when people browse your site they find nothing that makes them want to be customers, then you haven't really accomplished anything.
  • Use keywords. Customise the keywords and descriptions for each page on your site. This is hard work. If you can't be bothered doing it yourself then pay someone to do it for you.
  • Don't hide behind images. Use the 'alt' tag of an image to make sure that search engines knows what information it contains. For instance, if you always use an untagged graphic for the name of your product, search engines may never recognise that site as relevant to the product.
  • If you use frames, make sure you have a 'noframes' section. There are many myths about search engines not being able to search sites that use frames; the real problem is web designers who don't know how to use the noframes tag.

If this seems all a bit hard, there is an entire industry devoted to search engine optimisation (SEO) and its proponents will happily take your money; it might not make any difference, but if it makes you feel better…

My web site isn't working?

Perhaps not, but what did you expect?

Many web sites fail to meet the expectations of the site owners. There can be many reasons behind these perceived problems including poor design, hosting and telecommunications problems and even unrealistic expectations on the part of the site owner.

Web site review

Consider an external review of your website. Such a review should include:

  • Purpose Why do you have a web site? What do you expect from it? Why are these expectations not being met?
  • Design How does your web site appear to internet users?
  • Interoperability Can everyone use your site or only those using your favourite browser, screen size etc.
  • Usability Is your site difficult to navigate, difficult to use? Can people easily find what they are looking for?

A web site is not generally an end product but rather an element of an organisation's strategy. Web sites should not be created and then left to themselves (they do unspeakable things in the dark) but should be monitored and reviewed just like any other part of your business.

Add comment

Security code