dotnet / dotnet/Open-XML-SDK

ChangeDocumentType (.xlsm) to (.xlsx) not removing /xl/vbaProject.bin

Open
#1,209 13 comments 0 reactions 1 assignee Claimed by @tomjebo View on GitHub
bug more-information-needed no-issue-activity
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.