OpenOffice migration

I was chatting with a potential client (large, well known national chain) recently about a large migration project they were undertaking to migrate from MS Office to OpenOffice.

I explained that the spreadsheet stuff should migrate with few problems. Did they have any VBA I asked, ‘oh yes’ was the reply, lots of it, all over the place.

I said the trivial stuff would probably migrate ok, especially if they use one of the VBA friendly builds (OxygenOffice and Novell sprang to mind). The more complex stuff would realistically need re-writing I suggested, at which point he kind of lost interest. I did point out that it was a great opportunity to rewrite those apps in something more robust like .net but the lights had gone out by then.

VBA is the jewel in Microsofts Office lock-in crown, and they seem to act like a spoilt princess who is bored of diamonds, and now only wants emeralds or something. (And of course Office is the jewel in the operating system lock-in crown…)

If OpenOffice get highly compatible scripting or effective code migration tools before VBA’s replacement is broadly adopted, things are going to get fun in the spreadsheet development world. (They could do with a better coding environmet too, OO’s code window is even more arcane than the VBAIDE, (in fact it reminds me of Excel 5 modules)).

For many companies if print macros and navigation macros worked in OOo they could migrate probably 75% of their users, and just keep the real power users on Excel. All it would take is a few successful large scale migrations written up in the IT press and there could be an avalanche. I’m not clear why MS don’t seem concerned about this?

Anyone else getting sniffs of OO work?




8 Responses to “OpenOffice migration”

  1. Marcus Says:

    Many large organisations don’t appreciate the magnitude of the task. Part of their first step should be an audit of the spreadsheets they currently have in the wild. (Go ahead – ask them how many spreadsheets they have and then scan their network – they wont even be remotely close).

    How many? You don’t need to migrate all of them. If it hasn’t been updated in 12 months or so, can you justify why we need to migrate it (there’s always exceptions).

    Where are they? That’s right, including the big spreadsheet used to validate payroll that has spreadsheet and database links to half a dozen files on the payroll manager’s C:\ drive.

    How complex are they? How complex is the code? Do they interact with other systems?

    I think most organisation are dug deeper in to VBA than they realise and the process of extrication is a lot more involving than they care to admit.

    I recall seeing a cartoon years ago where one manager was talking to another about a company merger. One said to the: “I don’t think integration will be an issue. Their computers are pretty much the same colour as ours”.

  2. Harlan Grove Says:

    First in re migration, if you’re lucky enough to work for a company that’s subject to US civil law, you should already be aware of the general need to keep every file that has contributed to any data or information provided to any customer, analyst, regulator, tax authority, etc. in the past 20 years. I overstate only slightly. And if your company has entered into a consent decree with the US DOJ or some state attorneys general, you may as well simplify the task by archiving absolutely everything. Then there’s SOX.

    OOo could already serve the needs of most business PC users adequately. Most business PC users don’t create macros, and most of them seldom use any spreadsheets. I’d figure word processor use takes up 4 to 10 times more user time than spreadsheets. Where I work, the major spreadsheet applications already use VB.Net to run Excel workbooks, and the workbook windows are the only parts of the Excel UI shown.

    I was going to write that OOo couldn’t handle advanced array formulas or udfs, but, WTH, I hadn’t tried out 2.3.1 yet. OOo Calc now handles fairly complex array formulas and udfs returning arrays. I could have sworn this wasn’t so in 2.2.x.

    One bit of Excel syntax OOo doesn’t provide is entire column/row references, e.g., it translates the Excel reference 1:10 into $A1:$IV10. I can live with that. On the other hand, OOo Calc provides RELATIVE worksheet-qualified references with absolute worksheet references formed by prepending $ to worksheet names (what a concept!). And OOo provides built-in regular expression support.

    Aside from VBA, what can Excel 2003 do that OOo now can’t?

    For me this means the only things that could cause trouble in a migration would be event handlers and incomplete support for outside libraries/COM add-ins.

    That and OOo doesn’t provide a ribbon.

  3. Stephane Rodriguez Says:

    Simon said “If OpenOffice get highly compatible scripting or effective code migration tools before VBA’s replacement is broadly adopted, things are going to get fun in the spreadsheet development world.”

    Before this happens, Microsoft will sue the OpenOffice team to oblivion. This stuff has IP and Microsoft can’t let this be cloned reliably.

    Simon said “OO’s code window is even more arcane than the VBAIDE”

    Offtopic, but I’ve noticed that Excel 2007’s VBA IDE files are integrated in the product, rather than sitting in the CommonFiles/Microsoft shared/VBA/VBE.dll (+ a few satellite stuff). Hmmmm, may be they have smelled a threat…
    May be that has something to do with Microsoft ending the licensing of VBE to ISVs out there, so that they can’t plug it in OOo or elsewhere.

    Hmmm, again.

  4. Charles Says:

    To follow on from Harlan’s last point:

    I have always assumed that the major Microsoft strategy behind the ribbon was to try to prevent OOo from copying it by tying it up in legal IP tape, and to hope to have converted a sufficiently large mass of users to the ribbon and associated eye candy before OOo became a real threat.

    So you could look at the ribbon etc as trying to increase lock-in for casual users, and the continued life for VBA as trying to increase lock-in for more sophisticated users. And a combination of the two for large corporations who have both kinds of users.

    And then of course there is OOXML …

  5. Harlan Grove Says:

    In re OOXML.

    One possibility: Office 2007 and later DON’T support the ECMA standard (or ISO standard if ISO approves it) but instead use proprietary extensions, in which case the obvious move for Microsoft’s competitors would be to insist that governments accept only documents that strictly adhere to ISO (or maybe just ECMA) standard formats, in which case Office documents would be out in the cold unless Microsoft were to provide a Microsoft Office OOXML to ECMA/ISO OOXML converters. They could give such a product the code name ‘Kafka’.

    Another possibility: Office 20xx does support the ECMA standard in full, but so could any of Microsoft’s competitors. In the latter case, revisions to the OOXML standard would become the big issue, but I think the EU at least is now in a mood to fine Microsoft any time Office developers get access to standard revisions before their competitors do. Of course, better still would be to ensure a level playing field with new public file format standards – ban the sale of upgrade versions using revised standards until some set number of months after the standard revisions were approved.

    Either Microsoft is going to wind up looking [more] ridiculous or they’re really going to regret making their native format a publicly available standard.

  6. Simon Says:

    Marcus I think many orgs have now catalogued their important and above spreadsheets. They would need to do all the same stuff in moving Office version, the key difference between MS Office and A.nother Office is VBA.

    Stephane interesting point on the VBA integration.

    Charles I just can’t see the lock-in with the ribbon, it offers no additional functionality, less in-fact, than normal UI components. I think its just a fashion thing and will go the way of other fads pretty soon.

    Harlan It looks like I’m lagging in OOo I’m on 2.0 on my Eee and 2.2 on my Windows machines I think – time for an upgrade.
    I thought Sun had produced an add-in for MS Office so it could save to ODF?
    I think they are stuck on the file formats – damned if they do damned if they don’t.

  7. Charles Says:


    I was not trying to offer a value judgement on the ribbon, you seem quite capable on that front : but if you are a relative newby casual user and all you know is the ribbon then it would be a PITA to change to the classic menus, just as its a PITA to change to the ribbon from the classic menus.
    (and a double PITA to have some customers using one and other customers using the other).
    I think the ribbon will only go away if MSoft cease to think of it as an advantage against OOo.

  8. Simon Says:

    Sorry. What I was trying to say is I think we are basically making the same point. The ribbons value as a lockin tool is related to how genuinely useful it is over a
    standard UI. We maybe differ on how useful we think it is?

    I know what you mean on having customers on both. Its one of the main reasons I have paused xlanalyst development.

    I don’t have anything against the ribbon itself (much!), just the abusive way MS moved stuff around to destroy experienced users productivity.

    If all products move to a ribbon UI, and I mean pretty much everything that most of us use (MS and others), but OpenOffice etc are banned from using it, then I can imagine it being some sort of advantage.

    But I think the ribbon would need to be very widely adopted for that to happen, and in a way the licensing that cuts out OOo, may well discourage that wide adoption too.

    I actually think the ribbon in 2007 is the best thing that has ever happened to OOo. I think it will be responsible for the biggest boost in numbers of OOo deployments ever. So my advice to MS would be to get a classic option back ASAP before those OOo migration projects get past the feasibility stage.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: