OpenOffice Support

[diesel]

Originally, when I had read that there was an incompatibility of the most fundamental syntax of formulas in OpenOffice compared to Excel I was pissed and gave up on the project. I shared this info with my friends who also do developlment and they were shocked. They had no idea that OO was incompatible with an Excel file on such a basic level. Then I received Dave's comment on my blog and got recharged to try to tackle this task.

OpenOffice FAQ stating the basic incompatibility with Excel Formulas

Well, now that there is someone requesting it, and even willing to donate if I can do it. I've decided to pick up the torch and see what I can do. Editting each forumla by hand would be very time consuming and error prone, so my plan is to develop another macro that will update all the formulas in spreadsheet to use semi-colons (;) instead of commas (,).

In addition to this fundamental incompatibility the object model used in the macros is different from Excel, so I will need to update all the macros in the workbook to use the OpenOffice model. Once built I will need to maintain two separate files for Excel and OpenOffice.

If you have experience, please feel free to point me towards any useful tools/references. I'm basically going using the OpenOffice references and anything I can find in web searches to get this new file built. Cheers.

[diesel]

Need to write a macro to search the entire workbook for forumlas and update them all to use semicolons instead of commas.

Need to build a new macro for converting a recipe to the other version, so people using different applications can share recipes with each other. This should be a little easier once the first conversion macro is built.

Need to learn the OO Object Model and Update the Macros:
• Add2Inventory
• UpdateCalendar
• Add2Purchasing
• Export2JPEG
• Export2Recipes
• Export4Sharing
• FixNames
• ResetWorksheet
• SubtractInventory
• ToggleGravity
• ItemsPurchase
• SumBold
• SumUnderline
• SumItalic
• Unexpected Adjustments

Will need to maintain both sets of macros in both files so that they can export in each other's recipe format.

Testing Requirements:
• Test Each Macro
• Test Print Report
• Test Each Calculator
• Test Calendar - Each Duration, Each Recipe, Update
• Test Each Option of Worksheet
• Test Interoperability with Excel version

[seagull]

I just found your spreadsheet on brewboard. I use openoffice and can lend you as much time and effort as I can in this endeavor. However, I won't be able to do much between now and the 2nd week of June (getting married).

Send me an email.
thanks,
mike

[schmohead]

Hey all - the spreadsheet is awesome, although as I've pointed out elsewhere, Excel 2008 for Mac doesn't support any of the macros (support for VBA macros was pulled with the 2008 version). I've enjoyed using the main workspace for my first two AG batches - though I need to save a whole new file for each recipe, since the macros don't work. Let me know where I can donate!

Meanwhile, I would also be in favour of an OpenOffice version. Or even an Excel 2008 version that makes use of the new Applescript dictionary. Having some programming experience (though not with VBA or OpenOffice) I'm willing to bash away at an Excel 2008 version (I'm already somewhat familiar with Applescript, and would be more immediately useful there than deciphering OpenOffice's macro language) - or extensive testing of an OpenOffice version in development. Would need some help understanding the existing macros. Drop me a line, if any of this would be useful.

[diesel]

seagull, we chatted awhile back over email, i hope the wedding went off without a hitch (or should that be with a hitch).

thanks for the kind words, schmohead. I was reviewing my macros and normally I'm much better at commenting my code. I can be a comment freak sometimes. I'm going to look into putting up some sort of version control on the site and then we can check-in out macros perhaps. and then also publish official mods, like an OO port, and the Mac version with applescript instead of VBA.

for all those looking to help with the dev work, i'm going to put together a little documentation to explain the way around the code.

[schmohead]

Hi all - I've finally had time to take a closer look at the Applescript option for Excel 2008 Mac users. No deal-breakers, but it's a bit slow as I decipher the original VBA macros. The end result will be a folder of scripts that will appear in the application script menu on the Mac and will replicate the tasks of all the button-connected macros. We'll need to decide how to show those notes to Mac users who open the file and can't press the buttons.

Where are the last four macros you list above to be found? I pulled the first eleven put using a friend's copy of Excel 2007, but didn't see the last ones on your list.

[diesel]

schmohead, awesome. The very last one listed isn't a macro, just unexpected adjustments I figured would need to be made. The Sums are in a module called FormattingSums. They are shorter macros than all the others. Sorry for the kind of sloppy code, I never really got around to commenting it much.