Open Office Macros to Excel

[ExcelAtEverything]

Hello! It's been 5 yrs since I last used Excel as I'm in a different business now, but I was at that time I was a "fairly advanced" user I would say... but certainly not at true expert level. As of now however, I'd call myself a "rusty" user! LOL! I had to start a new account here as I cannot recall which of my many email accounts I previously used for this site. So that said, here is my question.

I am trying to build a spreadsheet for a friend, and all of my experience is in Excel ( I've never used Open Office for spreadsheets). Being that I'm currently strapped for cash, I have the free online version of Office 365 so that I can use Excel. I've since come to realize however, that this version will not allow me to write macros (which I need), so I'm trying to find a way around this. My plan is to try and build most of the sheet using Office 365, then transfer it to Open Office to add the macros so that the sheet works as designed.

Being that I don't want to do all of that only to find out at the end that it cannot effectively be done, are there any hiccups or restrictions that I will face which will prevent me from doing this as planned? Or is there a better and easier way that I am not aware of?

Thanks so much!

 

[Michael M]

I don't believe you can "get around" the online versions lack of the Developer Tab......I was looking at upgrading to 365 for my new laptop and found the only way to get the Developer Tab was to subscribe to the PC version of 365.
Others may have had better experience and success....but not me I'm afraid ...

 

[ExcelAtEverything]

Thanks for the swift reply! Is that to say though, that I cannot simply open the Excel sheet in Open Office, add the macro there, then export it as an Excel file? At that point then it would seem to me that someone who had the paid version of Excel could easily run the macro. Am I thinking incorrectly?

 

[Michael M]

No, Open Office to EXcel sounds OK......but not the reverse procedure !
I'm not sure though that without the Developer Tab in Excel it won't come across as an .xlsm file !

 

[eduzs]

Unfortunately, as far as I know there's no automatic conversion of workbooks with macros/vba from excel to open office and vice versa within simple save-as or export file menu.

 

[ExcelAtEverything]

Well that's a bummer.

 

[Michael M]

Yeah....Microsoft can't afford to make anything useable, cheap OR for nothing...

 

[Dan_W]

Your question got me curious, because I do remember reading something about VBA interoperability with Open Office and seeing that WPS Office (for example) had its own Macros section, so I did a little digging. Apache Open Office says that it can import VBA code and run it natively, but that the same cannot be said for export to MS Excel files. Check out: https://wiki.openoffice.org/wiki/VBA_interoperability_in_OpenOffice

VBA is an implementation of Microsoft's Visual Basic language, an event-driven programming language which is built into most Microsoft Office applications. OpenOffice can run Microsoft VBA code natively, though there are some APIs of VBA that are not supported, or are only partially supported. ...
Now we can import MS Excel 2003 and MS Excel 2007(xlsm/xlsb) with no modification, it includes:

• Import VBA code in Modules, Dialog and Class Modules.
• Attach and Enable to Run all the Workbook and Worksheet events.
• Enable the Whole VBA Runtime Environment.
• Support to import VBA Userform Controls.
• Attach and Enable all the Userform Controls events.

As regards exports back into MS docs:

Export
Unfortunately, OpenOffice can't support export VBA back to MS Excel files, so Export may be the important and interesting area in VBA interoperability. It can support aspects such as:

• Export VBA Macros in Document Objects and Modules
• Ensure VBA Macros Workable in Excel Files
• Export VBA Macros of VBA Userform Controls into Excel Files
• Export VBA Userform Controls

Thinking outside the box, it would depend on what it is you would want to do, but you can use VBScript to manipulate Excel spreadsheets. Another alternative, which I've started to explore recently, is Autohotkey.

 

[Jon Peltier]

If your friend will be using a desktop version of Excel, and the solution requires VBA, that is what you need to develop the solution in. VBA is only supported in Excel for Windows and Mac, not Online. I would hesitate to use a "free" substitute for Excel. Their implementation of "macros" is likely to be different, and even their formulas and functions may not be the same.

 

[ExcelAtEverything]

Thanks everyone. I ended up just getting Excel for Windows 2019 Home/Student. Lol
Too many issues to worry about it.