Bug 105069 - Cannot save a new VBA module or worksheet in an existing XLS or XLSX file
Summary: Cannot save a new VBA module or worksheet in an existing XLS or XLSX file
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
5.2.4.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Markus Mohrhard
URL:
Whiteboard: target:5.4.0 target:5.3.3
Keywords:
Depends on:
Blocks:
 
Reported: 2017-01-03 16:10 UTC by pgkos
Modified: 2017-04-20 04:31 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
An empty XLS file created in Excel 2003 with embedded VBAProject (9.00 KB, application/vnd.ms-excel)
2017-01-04 12:58 UTC, pgkos
Details
gdb backtrace (9.40 KB, text/plain)
2017-01-04 17:39 UTC, pgkos
Details

Note You need to log in before you can comment on or make changes to this bug.
Description pgkos 2017-01-03 16:10:25 UTC
LibreOffice Calc 5.2.4.2.0 20m0(Build:2) on Arch Linux 64-bit.

It is impossible to add and save a new VBA module in an existing
XLS or XLSX file (created in Excel, macros inside VBAProject).

Also, when a new worksheet is created in Libreoffice, it is impossible
to save a VBA macro attached in this new worksheet.

Additionally, when trying to save an XLS (old format) file, an "Input-output error" message box appears and libreoffice hangs (cannot exit, have to kill soffice.bin).

Steps to reproduce:
1. Open an XLS file (created in Excel, with VBAProject inside) and open LibreOffice Calc's Basic editor.
2. Click on "Select Module". Add a new module in the VBAProject->Modules.
3. Add a few lines of code to the new module.
4. Try to save the file.
Comment 1 Jacques Guilleron 2017-01-03 18:42:20 UTC Comment hidden (obsolete)
Comment 2 Markus Mohrhard 2017-01-03 20:54:12 UTC
(In reply to Jacques Guilleron from comment #1)
> Hi pgkos,
> 
> VBA and Basic Language used into LibreOficce differ.
> See here how to import, use or transform those codes:
> https://help.libreoffice.org/Common/Using_Microsoft_Office_and_LibreOffice
> 


LibreOffice supports VBA and since 5.1 even supports saving back changed VBA streams.
Comment 3 Buovjaga 2017-01-04 11:56:01 UTC
(In reply to pgkos from comment #0)
> 1. Open an XLS file (created in Excel, with VBAProject inside) and open
> LibreOffice Calc's Basic editor.

Please attach such an example file.

Set to NEEDINFO.
Change back to UNCONFIRMED after you have provided the document.
Comment 4 pgkos 2017-01-04 12:58:43 UTC
Created attachment 130149 [details]
An empty XLS file created in Excel 2003 with embedded VBAProject

I am attaching a simple XLS file created in Excel 2003 which contains an embedded VBAProject.
Comment 5 pgkos 2017-01-04 17:39:14 UTC
Created attachment 130154 [details]
gdb backtrace

The bug occurs also on the newest LibreOfficeDev 5.4.0.0.alpha0.

I am attaching a gdb backtrace.
Comment 6 Buovjaga 2017-01-04 19:37:49 UTC
More detailed steps:

1. Open example file, go to Tools - Macros - Organize dialogs
2. Go to tab Modules, expand excel_2003_vba > VBAProject > Modules
3. Click New, OK to create new Module1 under Modules
4. Click Edit, add some Rem comments or whatever
5. Try to save as XLS

General Error.
General input/output error.

LibO UI turns grey, top menus works, but items are disabled.. have to kill process.

Arch Linux 64-bit, KDE Plasma 5
Version: 5.4.0.0.alpha0+
Build ID: 1a58cdf8af1aba52ce0a376666dd7d742234d7cf
CPU Threads: 8; OS Version: Linux 4.8; UI Render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on January 4th 2016
Comment 7 Markus Mohrhard 2017-01-04 22:57:43 UTC
This is a problem coming from the VBA export work. In the old code we could not export the changed VBA stream so it was not a problem that we never correctly added new modules to the VBA part of the basic library. I think I have a simple fix for that.
Comment 8 Commit Notification 2017-01-05 01:11:12 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2be139d76bdb66e77719613d802dc0047c284456

tdf#105069, store the VBA module info for new modules as well

It will be available in 5.4.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 pgkos 2017-01-05 13:18:32 UTC
Hello Markus,

Thank you for the patch. It fixes the issue with modules inside XLS files. It is now possible to add and save new modules.

There is one small bug left. When I add a new worksheet to an XLS file and then add a macro to this worksheet, and save the file, the worksheet macro module is not recognized properly by Excel. (it shows up as a duplicate sheet module with a wrong icon - the same as of ThisWorkbook module)

I suppose that this is caused by not including a CodeName record in the new worksheet substream (signature 0x01ba, EXC_ID_CODENAME).
Comment 10 Markus Mohrhard 2017-01-06 03:57:05 UTC
(In reply to pgkos from comment #9)
> Hello Markus,
> 
> Thank you for the patch. It fixes the issue with modules inside XLS files.
> It is now possible to add and save new modules.
> 
> There is one small bug left. When I add a new worksheet to an XLS file and
> then add a macro to this worksheet, and save the file, the worksheet macro
> module is not recognized properly by Excel. (it shows up as a duplicate
> sheet module with a wrong icon - the same as of ThisWorkbook module)
> 
> I suppose that this is caused by not including a CodeName record in the new
> worksheet substream (signature 0x01ba, EXC_ID_CODENAME).

That is expected as worksheets are much more complicated to handle that normal modules. I have not yet an idea how to recognize them in the basic code.
Comment 11 Markus Mohrhard 2017-03-14 17:37:31 UTC
(In reply to pgkos from comment #9)
> Hello Markus,
> 
> Thank you for the patch. It fixes the issue with modules inside XLS files.
> It is now possible to add and save new modules.
> 
> There is one small bug left. When I add a new worksheet to an XLS file and
> then add a macro to this worksheet, and save the file, the worksheet macro
> module is not recognized properly by Excel. (it shows up as a duplicate
> sheet module with a wrong icon - the same as of ThisWorkbook module)
> 
> I suppose that this is caused by not including a CodeName record in the new
> worksheet substream (signature 0x01ba, EXC_ID_CODENAME).

Can we maybe create a new bug report for this problem. Having independent bug reports (they can still be linked with the "See Also" feature) helps in tracking which bugs I still need to handle.
Comment 12 Commit Notification 2017-03-28 20:08:14 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=fd242f02b195f896b23336fe51773d7abf310d4e&h=libreoffice-5-3

tdf#105069, store the VBA module info for new modules as well

It will be available in 5.3.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.