Page 1 of 1

Excel with macros to Calc

PostPosted: Mon May 27, 2019 5:41 pm
by Polytrop
Hello everyone.
When opening an Excel (latest version) file with two macros in Calc the result is sadly too bad. Graphical elements such as background colours are messy and most of all, the Excel macros does not seem to work in Calc. I suppose this might be a common issue. Anyway to solve the problems? Thanks in advance.

Re: Excel with makros to Calc

PostPosted: Mon May 27, 2019 6:01 pm
by Zizi64
The MS VBA macros are not compatible with the OpenOffice/LibreOffice API and the StarBasic.

- You must rewrite all of your VBA macros if you want to use them in the AOO or LO.
- Try the LibreOffice: it has a littlebit higher compatibility vith the VBA macros.

Please upload your sample spreadsheet file here with the embedded macros.

Re: Excel with macros to Calc

PostPosted: Tue May 28, 2019 9:36 pm
by Polytrop
Beläggning Gästis, aktuell ver2.xlsm
Excel file with two macros
(50.18 KiB) Downloaded 37 times


The spreadsheet handles data for a small booking system. One macro sorts the list in chronological order the other by the name of the guest.
The table to the right shows monthly summaries. The sheet is locked and there is no password to lock it up.

Re: Excel with macros to Calc

PostPosted: Wed May 29, 2019 1:19 am
by Lupp
(Using "macros" for rather banal steps in a workflow seems to be an effective means of commercial providers of office software in the struggle for user close-in.)

There are only two actual subroutines in the pompous "VBA projcet", and both seem to be recorded from a manually performed sorting.
The range for sorting is constant in both cases, but the ranges are not consistent: A6:H115 in the first case, A6:H121 in the second.

As soon as you need a range going beyond row 115 or rename the sheet to a different name, the sub will no longer work as expected / at all. And starting with the empty row 6 may not be the best idea.

LibreOffice will not run these specific macros.

To record macros for the purpose is also possible in LibO Calc (and most likely also in AOO).
A seriouis solution should avoid this. Identify the sheet and the range to work on setting the .Tag property of the used form controlls, e.g.

To avoid the need of selecting the range for sorting before performing the sort, you would need to develop the macros without using the dispatcher.

See attachment.