Bug 118146 - VBA Macro: Application.ActiveWorkbook.SaveAs saves invalid files
Summary: VBA Macro: Application.ActiveWorkbook.SaveAs saves invalid files
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
(earliest affected)
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Macro-VBA
  Show dependency treegraph
Reported: 2018-06-13 15:52 UTC by Gabor Kelemen
Modified: 2021-02-11 15:41 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:

Example file from Excel (25.60 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2018-06-13 15:52 UTC, Gabor Kelemen
Example file saved with the macro from Calc (16.00 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-06-13 15:52 UTC, Gabor Kelemen
Error messave when trying to open the file in Excel (183.89 KB, image/png)
2018-06-13 15:54 UTC, Gabor Kelemen

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 2018-06-13 15:52:14 UTC
Created attachment 142708 [details]
Example file from Excel

There is a VBA macro in the attached file which exports and saves the sheets to individual/new Workbooks. The main command which will “Save As” the sheets is: Application.ActiveWorkbook.SaveAs FILENAME, FILEFORMAT ; 
The FILEFORMAT tag was ignored or LibreOffice did not recognize the FILEFORMAT number (in case of *.xlsx →51). It is a problem when we use a macro in an *.xlsm/xls file and would like to export the sheets AS *.xlsx. The VBA Project is not removed properly from the new files and the file extension is wrong according to Microsoft Excel. (Zip file error). The exported sheets opened correctly with LibreOffice Calc, but the VBA Project was still there without any Objects.

Steps to reproduce:
1. Open the attached spreadsheet (“Makro_Save_as_Sheets.xlsm”) with LibreOffice Calc
2. Run the macro and try to open the exported file with Microsoft Excel.

This is the key command which does the export:
xFile = FolderName & "\" & Application.ActiveWorkbook.Sheets(1).Name & datum & ".xlsx"
Application.ActiveWorkbook.SaveAs xFile, FileFormat:=51

Actual results:
If the source file (which included the macro) was an *.xlsm/xls file LibreOffice exports the VBA Project even if we set the export format to *.xlsx. When we try to open with Microsoft Excel, we get an error message. (BAD FORMAT) The exported sheets opened correctly with LibreOffice Calc, but the VBA Project was there without any Objects.

Expected results:
We should not get an error message and the “Application.ActiveWorkbook.SaveAs xFile, FileFormat:=51” command should work correctly even if the source file was a different type. (BAD FORMAT)

Build ID: 1e9e01dff0a8d65bb10d5a886cca7899b43979da
CPU threads: 4; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-05-27_05:50:28
Locale: hu-HU (hu_HU); Calc: group threaded
Comment 1 Gabor Kelemen 2018-06-13 15:52:51 UTC
Created attachment 142709 [details]
Example file saved with the macro from Calc
Comment 2 Gabor Kelemen 2018-06-13 15:54:44 UTC
Created attachment 142710 [details]
Error messave when trying to open the file in Excel

~ "Excel can't open the file, because it has an invalid extension or format. Check the file integrity and that the content matches the extension"
Comment 4 Julien Nabet 2018-06-15 09:30:03 UTC
(In reply to himajin100000 from comment #3)
> https://opengrok.libreoffice.org/xref/core/oovbaapi/ooo/vba/excel/
> XlFileFormat.idl?r=3656a57e#20
Following https://msdn.microsoft.com/fr-fr/vba/excel-vba/articles/xlfileformat-enumeration-excel, it seems that adding xlOpenXMLWorkbook (which has 51 for value) would help here.
Comment 5 Julien Nabet 2018-06-15 09:33:11 UTC
+ use the new constants at different locations quoted by Himajin (and also indicated by https://opengrok.libreoffice.org/search?project=core&q=xlExcel9795&defs=&refs=&path=&hist=&type=)
Comment 6 Gabor Kelemen 2018-06-18 10:38:26 UTC Comment hidden (obsolete)
Comment 7 Gabor Kelemen 2018-06-18 10:39:18 UTC
Thanks for the code pointers. Seems like an easyhack.

@Balazs, you might be interested in this one :).
Comment 9 QA Administrators 2019-10-12 02:44:19 UTC
Dear Gabor Kelemen,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)

If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword

Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team