Calc not preserving original VBA macros


In my company, we use an ERP which allows the use of a MS .xls template to upload bulk data, accounting bookings to be specific. This Excel 97-2003 file is heavily loaded with macros and forms. Its empty (for the user) size is 550KB.

In order to automate some tasks, I have python code for different processes, some of which require the filling of such templates, but leaving the internal macros untouched, as they are used to connect to the ERP.

Time ago, I did these processes using a raspberry pi, which came with LibreOffice 4.2 in the Raspbian pack. I figured how to make it work headless, and if I did it with the UI, at the moment of saving the template in MS Excel 97-2003 a message was displayed “All changes to the basic code are lost. The original VBA macro code is saved instead”. This was perfect for me, as when the template was distributed and the users uploaded it using MS Excel, they found no trouble.

Nowadays I’m using a more decent machine with Ubuntu 18.04, with LibreOffice For “normal” spreadsheets it works whitout problems. But for the templates case the behaviour has changed. There is no more “could not write” message, now it seems that LO is able to write in the code (I have seen headings like “Option VBASupport 1”, or worse, all the code commented), so it doesn’t preserve the original VBA anymore. When I try to open the resulting files from MS Excel, lots of error messages appear.

I have tried all possible combinations of the options in Tools/Options/Load/Save/VBA Properties and Microsoft Office with no luck. When saving as, the saved file is reduced to 118 KB (originally 550 KB), even if I just open it and save as xls 97-2003. Looks like some bug fix has crashed my uses…

Am I missing any detail in the process? Right now, the best solution I can think of is to try to install LO 4.2

Thank you very much in advance for your time. I know my request may seem strange (2020 and uploading bookings with a xls file…) but that’s the way things are.