Excel with macros to Calc

Creating a macro - Writing a Script - Using the API

Excel with macros to Calc

Postby Polytrop » Mon May 27, 2019 5:41 pm

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
Polytrop
 
Posts: 2
Joined: Mon May 27, 2019 5:23 pm

Re: Excel with makros to Calc

Postby Zizi64 » Mon May 27, 2019 6:01 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8147
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Excel with macros to Calc

Postby Polytrop » Tue May 28, 2019 9:36 pm

Beläggning Gästis, aktuell ver2.xlsm
Excel file with two macros
(50.18 KiB) Downloaded 32 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
Polytrop
 
Posts: 2
Joined: Mon May 27, 2019 5:23 pm

Re: Excel with macros to Calc

Postby Lupp » Wed May 29, 2019 1:19 am

(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 27 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2523
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests