Bug 53976 - XLS FILESAVE: File loses it's ActiveX VBA functionality (lost form) after saving in latest version(after 3.4)
Summary: XLS FILESAVE: File loses it's ActiveX VBA functionality (lost form) after sav...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.0.1 rc
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
: 47231 (view as bug list)
Depends on:
Blocks: Macro-VBA
  Show dependency tree / graph
 
Reported: 2012-08-23 18:12 UTC by Alex
Modified: 2023-05-17 11:45 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Attachments
test case of bug (672.70 KB, application/x-7z-compressed)
2012-09-06 04:49 UTC, sasha.libreoffice 		Details
simplified testcases (15.48 KB, application/x-7z-compressed)
2012-09-11 13:47 UTC, sasha.libreoffice 		Details
Show Obsolete (1) View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Alex 2012-08-23 18:12:14 UTC 
Problem description: 
I am using MS Excel files under libreOffice.
Until version 3.5 i could use VBA functionality.
In latest version, VBA doesn't work after saving the file.
Once I save the file in LO 3.5 and higher, it's VBA blocks.
If I use older versions, everything is fine.

 Is it made deliberately or it;s just a bug ?
Comment 1 sasha.libreoffice 2012-08-30 08:35:18 UTC 
Thanks for bugreport.
I have received file "Levski 24.08.2012.xls". But I do not know if I allowed it to attach here or it contains confidential information.
Also, I do not know how to us it. I need this information for testing what is broken.
Comment 2 sasha.libreoffice 2012-09-06 04:47:25 UTC 
Alex replied by Email:
Yes, You are allowed to attach the file here.The is no confidential information in it. It is a simple account program for a small office, but it is just a test copy.
There are buttons on the sheet "Kasa" and when i use the file under LO 3.4 they are connected to macros, and in latest versions, they just don't work.
Or at first they work and i when safe the file and close it and start it again, the buttons don't execute the macros.
Comment 3 sasha.libreoffice 2012-09-06 04:49:13 UTC 
Created attachment 66703 [details]
test case of bug
Comment 4 sasha.libreoffice 2012-09-06 08:54:52 UTC 
in 3.3.4 when I click buttons on first sheet, appears form.
in 3.5.5 and 3.6.1 nothing happens

in msExcel 2003 and 2007 nothing happens. It looks like it is in Form-edit-mode. I do not know how to force Excel to execute macros after clicking on buttons on sheet. It needed to simplify test document.
Comment 5 sasha.libreoffice 2012-09-11 13:47:43 UTC 
Created attachment 66973 [details]
simplified testcases

Reply from Alex:
This is my problem.The file works under LO 3.4 and doesn't work under latest versions.
When i save it under LO , close it and open under Excel, the macros don't work.
I send You four files.They are quite simple. I just put one button, which shows one of my forms.
The first file "test.xls" is the original.It works fine under excel and LO 3.4.
The second file "test Lo.xls" is the same file, saved under LO 3.5.It still works under LO 3.4, but don't under Excel and LO 3.5.(at first, when I opened it under LO 3.5 it worked, but when i saved it, ti stoped.)
You can try open the copy of the original file under LO 3.5 or 3.6. After test it, save it and close it and try after that with excel and LO 3.5/3.6.
May be the problem is in the form ? Files test2 and test2 LO are simplified.I use a very simple new form.The result is the same.The first file "test2.xls" is the original. The second file :test2 LO.xls" is the same file, saved under LO 3.5. After closing it, macros don"t want to work under excel and Lo 3.5/3.6 but stil works under LO 3.4.
Comment 6 sasha.libreoffice 2012-09-11 14:46:00 UTC 
Thanks for simplified test case
(There is button on first sheet, after clicking on it appears form)
Result of my experiments with file test.xls:

In 3.3.4 and 3.4.2: after saving this file, it still working in Calc, but not working in Excel.
In 3.5.5 and 3.6.1: after saving this file it not working in both Calc and Excel (but works in Calc 3.3.4)

What is interesting: if in Calc 3.6.1 copy-paste button from working file to unworking, then it works there.

It looks like regression, at least from user point of view.
Comment 7 Noel Power 2012-09-17 11:30:55 UTC 
no this is not a bug, nor imho regression ( although it does look like one ) Let me try to explain, there are 2 types of controls used in excel documents, There are form controls and activex controls, form controls can have a single macro associated with them ( and there is actually a hard binding in the file format to specify that ) ActiveX controls on the other hand don't have a single macro associated with them they instead allow macro handlers to be defined in the code, e.g. a macro to handle a click or a mouse over etc. The application looks for the handler when the associated event happens and then fires it. Now the ( Test.xls ) document in question contains a single button and it is an activex button contro, when we save the document ( as xls ) we cannot save activex control, instead we have to convert the button to a form control, there is no associated macro in this case ( remember the control we are exporting is an activex control that doesn't have macro bindings ). 
I don't recall that activex control import ever tried to fake the event handling by trying to create a macro bindings based on the presence of event handlers in the source code, it's possible that openoffice code did this for a while, but.. they in the end decided to take our code that allows the imported control to dynamically run the event handler macros ( if they are present ) for the appropriate events ( like a button click ). Note: if you used form control buttons ( with the appropriate macro binding ) in the source xls document those macro bindings would be exported correctly.
Comment 8 sasha.libreoffice 2012-09-17 11:54:45 UTC 
Thanks for explanation about source of problems and about difference in handling between ActiveX and Form Control.
And what decision?
Comment 9 Noel Power 2012-09-17 13:20:01 UTC 
like I said, this behaviour is as a result of better import capability and removal of some rather some poor import behaviour. The real solution here would be to be able to properly export activex controls. There is no plan to work on that afaik and it would not be trivial ( iirc )
Comment 10 sasha.libreoffice 2013-05-29 11:38:37 UTC 
*** Bug 47231 has been marked as a duplicate of this bug. ***
Comment 11 QA Administrators 2015-03-04 02:20:52 UTC Comment hidden (obsolete)
Comment 12 Buovjaga 2015-03-22 13:36:23 UTC 
(In reply to sasha.libreoffice from comment #6)
> Thanks for simplified test case
> (There is button on first sheet, after clicking on it appears form)
> Result of my experiments with file test.xls:

Button does nothing for me in LibO. Tested all files.

Win 7 Pro 64-bit, LibO Version: 4.4.1.2
Build ID: 45e2de17089c24a1fa810c8f975a7171ba4cd432
Locale: fi_FI
Comment 13 tommy27 2016-04-16 07:23:15 UTC Comment hidden (obsolete)
Comment 14 Justin L 2017-02-23 16:43:06 UTC 
Confirmed still a problem in 5.4.  (The button works again somewhere between late 5.2 or early 5.3.)
Steps:
1. Open document and verify that the button opens a form.
2. Save document.
3. Open document.  Error occurs when the button is pressed.
Comment 15 QA Administrators 2018-07-24 02:37:34 UTC Comment hidden (obsolete)
Comment 16 QA Administrators 2020-07-24 03:46:10 UTC Comment hidden (obsolete)
Comment 17 QA Administrators 2022-07-25 03:27:34 UTC Comment hidden (obsolete, spam)
Comment 18 Justin L 2022-09-25 01:47:11 UTC 
Repro 7.5+
-buttons work and form opens in original file.
-error after pushing the button on a round-tripped file.
Comment 19 Justin L 2023-05-17 11:45:07 UTC 
repro 7.6+
-simplified testcases: Test.xls round-trip: Error running MenuForm.Show ("Property or method not found: Show")

Same problem with Test2.xls