What do I need to do in order to get OpenOffice.org Calc to read the VBA code in an Excel file?

views:

197

answers:

+3 Q:

What do I need to do in order to get OpenOffice.org Calc to read the VBA code in an Excel file?

My employer is considering installing OpenOffice.org's suite of Office products instead of using Microsoft Office. I've written several advanced applications in Access and Excel, and I'm trying to figure out what will and will not be compatable with OpenOffice.org.

Specifically, what do I need to do to have Calc (OpenOffice.org's version of Excel) read and execute VBA in macro's and tie macro events to button clicks?

Also of note, I'm using the MySQL Connector from within Excel to dynamically update data in the spreadsheet. What might I need to do to preserve data connectivity to our MySQL server?

Thanks

+5 A:

As far as I know, OOo Base can connect to an mdb file to the extent that it can open tables and queries. I think anything else - forms, reqportes, macros VBA and native - would need to be redeveloped.

I don't believe that OOo Calc can read or execute VBA: it has its own dialect of Basic, which has a variant syntax. It doesn't, as far as I can tell, support the concept of Class, for example. Some work appears to be occurring, however, see the OpenOffice Wiki for more information. Right now, I'd say you should be planning on a careful and complete rewrite.

Regarding the MySQL connector - I'd say that's the least of your worries...

On the plus side, you're likely to have real job security for a year or two while you get everything ported!

Mike Woodhouse 2009-10-30 20:08:42

Thanks for your suggestions and comments. I did find an option in Tools > Options > Load/Save > VBA Properties that allowed me to turn on Executable code. However, it didn't even recognize the query table object so I'd be rewriting that functionality anyways.So yes, it does look like I'll have lots of job security if we go the "free" route :-).

Ben McCormack 2009-10-30 20:26:18

envalid 2009-10-31 18:46:37

That is a good suggestion, but then I'd have to write 100% bug-free code :-). Any VBA exception in the access runtime causes the application to just close. I wish I could say my code was perfect, but usually my solutions are good enough to get the job done, but occasionally show a bug when I user does something unexpected that I didn't catch. For the type of applications I've been writing, it's usually OK, but not always. I'll check it out either way. Who knows; maybe it will work!

Ben McCormack 2009-11-01 22:10:15

@Ben - try using `On Error Goto` to catch unhandled errors, that should prevent VBA exceptions from causing the application to close. You can use free tools like MZTools to add error handlers automatically

MarkJ 2010-01-08 15:41:21

+1 A:

I recently discoverd go-oo which seems to be able to achieve your task.

Aif 2009-10-31 18:42:55

ansaurus

tags:

views:

answers:

What do I need to do in order to get OpenOffice.org Calc to read the VBA code in an Excel file?

related questions