Excel with macros to Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Polytrop
Posts: 2
Joined: Mon May 27, 2019 5:23 pm

Excel with macros to Calc

Post 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.
Matts W
Open office 4.1.6 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Excel with makros to Calc

Post 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.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Polytrop
Posts: 2
Joined: Mon May 27, 2019 5:23 pm

Re: Excel with macros to Calc

Post by Polytrop »

Beläggning Gästis, aktuell ver2.xlsm
Excel file with two macros
(50.18 KiB) Downloaded 149 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.
Last edited by robleyd on Wed May 29, 2019 2:49 am, edited 1 time in total.
Reason: Remove duplicate attachment
Matts W
Open office 4.1.6 on Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Excel with macros to Calc

Post 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.
Attachments
nordic_1.ods
(41.74 KiB) Downloaded 163 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply