ChangeDocumentType (.xlsm) to (.xlsx) not removing /xl/vbaProject.bin
- Dominant language
- C#
- Stars
- 4.6k
- Forks
- 605
- PR merge metrics
- No merged PRs in 30d
Description
**Describe the bug**
Using ChangeDocumentType from MacroEnabledWorkbook (.xlsm) to Workbook (.xlsx) results in the error in Excel and spreadsheet cannot be opened (loosely translated from another language):
"The files does not have any macros, but it contains macro-based content"
When looking at the XML of the new .xlsx spreadsheet, the spreadsheet contains the file /xl/vbaProject.bin, which I think it should not. When removing this file, Excel will no longer give error preventing the user from opening the spreadsheet.
I use this code to change document type:
```
public void Convert_to_OOXML_Transitional(string input_filepath, string output_filepath)
{
byte[] byteArray = File.ReadAllBytes(input_filepath);
using (MemoryStream stream = new MemoryStream())
{
stream.Write(byteArray, 0, (int)byteArray.Length);
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(stream, true))
{
spreadsheet.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
}
File.WriteAllBytes(output_filepath, stream.ToArray());
}
}
```
I can fix the problem with the following code:
```
public void Remove_VBA(string filepath)
{
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, true))
{
VbaProjectPart vba = spreadsheet.WorkbookPart.VbaProjectPart;
if (vba != null)
{
spreadsheet.WorkbookPart.DeletePart(vba);
}
}
}
```
The problem can also relate to MacroEnabledTemplate (.xltm) spreadsheets but I have not tested this.
Even though I can fix the problem in my code, I think this should be fixed in Open XML SDK.
**Screenshots**
If applicable, add screenshots to help explain your problem.
**To Reproduce**
1. Run the ChangeDocumentType code on my linked data sample
3. Run the fix to see if it works
**Observed behavior**
VBA project file /xl/vbaProject.bin is not removed from spreadsheet when using ChangeDocumentType from .xlsm to .xlsx. The problem could also relate to MacroEnabled Temaplte (.xltm), but I have not checked this.
**Expected behavior**
It should be removed, because Excel gives error when trying to open spreadsheet that has been changed from .xlsm to .xlsx. When removing /xl/vbaProject.bin Excel does no longer give error.
**Desktop (please complete the following information):**
- OS: Windows 11
- Office version 2208
- .NET Target: .NET Core 6.0.9
- DocumentFormat.OpenXml Version: 2.18.0
**Additional context**
I refer to data sample (.xlsm) that has this error, if you try to ChangeDocumentType.
https://github.com/Asbjoedt/CLISC/blob/master/Docs/Example.xlsm
Contributor guide
Assessment
This issue has not been assessed yet.