Bug 118146 - VBA Macro: Application.ActiveWorkbook.SaveAs changes file format from xlsm to xls
Summary: VBA Macro: Application.ActiveWorkbook.SaveAs changes file format from xlsm to...
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 (allotropia)
Modified: 2023-02-13 15:52 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:
Regression By:

Example file from Excel (25.60 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2018-06-13 15:52 UTC, Gabor Kelemen (allotropia)
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 (allotropia)
Error messave when trying to open the file in Excel (183.89 KB, image/png)
2018-06-13 15:54 UTC, Gabor Kelemen (allotropia)

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen (allotropia) 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 (allotropia) 2018-06-13 15:52:51 UTC
Created attachment 142709 [details]
Example file saved with the macro from Calc
Comment 2 Gabor Kelemen (allotropia) 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 (allotropia) 2018-06-18 10:38:26 UTC Comment hidden (obsolete)
Comment 7 Gabor Kelemen (allotropia) 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 Comment hidden (obsolete, spam)
Comment 10 QA Administrators 2023-02-12 03:20:54 UTC Comment hidden (obsolete)
Comment 11 Gabor Kelemen (allotropia) 2023-02-13 10:58:49 UTC
This is still a problem in

Version: (X86_64) / LibreOffice Community
Build ID: 14a36ad49518bcb5b606b0f1640e3ca56b636e89
CPU threads: 14; OS: Windows 10.0 Build 19045; UI render: default; VCL: win
Locale: en-US (hu_HU); UI: en-US
Calc: threaded
Comment 12 Gabor Kelemen (allotropia) 2023-02-13 15:52:57 UTC
A bit more investigation in this area revealed that Calc changed the file format:
renaming the generated files to xls makes Excel happily open them.