Saving MemoryStream will Remove Macro Assignment

Aspose.Cells Product Family
1

Hello,
We are manipulating an xlsm file. This is just a simple file with macro, several ranges, and a button with macro assigned to it.Very simple, like:
Sub Button1_Click()
Range(“RangeA1”).Select
Selection.Copy
Range(“RangeB1”).Select
ActiveSheet.Paste
End Sub

So we load the xlsm’s bytes to Workbook, update some ranges, convert Workbook back to bytes, convert it back from bytes to Workbook, and then we save to stream as Xlsm

using (Stream stream = new MemoryStream())
{
workbookResult.Save(stream, new OoxmlSaveOptions(SaveFormat.Xlsm));

still there, we chunk the stream to bytes so that we can send them to another system

int offset = 0;
stream.Position = 0;
byte[] buffer = new byte[stream.Length];

while (offset < buffer.Length)
{
int count = System.Math.Min(buffer.Length - offset, 16 * 1024);
offset += stream.Read(buffer, offset, count);
}


Trying to save the new bytes like this:
File.WriteAllBytes(@“c:\buffer.xlsm”, buffer);

will give us a xlsm file with all changes, macro, but button inside that has no macro assignment anymore. So you will find that Button1_Click() but not assigned to anyone.

Surely if you just save the workbook, you won’t find this bug. Except that’s not what we want to do here. Also, please note that this is working perfectly with XLS format and macro.

Sample code, unit test, and xlsm attached.

Please advise, need urgently

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

I have tested your xlsm file with the latest version:
Aspose.Cells
for .NET v7.2.2.2
and found that macros are not lost.

Please download and use it and let us know your feedback.

Please see the attached output xlsm file generated by the code you have provided.

3

Unfortunately, we ARE using 7.2.2.2 because we know version 7.0.3 will strip off macro

And yes, the macro is there but not the assignment. If you just run the code I attached, or at least look at sample xlsm result I attach 12569_buffer.xlsm (in zip) then you will see

4

Hi,

Thanks for your feedback.

Did you mean to say, when we click on the button, macros are not executed? I was able to notice this problem.

Could you please clarify it with a screenshot if I am missing anything?

We will look into it and fix the problems.


5

Exactly. Macro is not going to execute if you click that button, because there is no macro assigned to it.

It's been stripped off! That's what I mean with "Macro Assignment"

6

Hi,


I have checked and tested your case using your project with v7.2.2. You are doing a minor mistake I think. Please change your method i.e.

public static byte[] ConvertWorkbookToBytes(Workbook workbook)
{
//save the generated workbook to a byte array via a memory stream
using (MemoryStream streamWorkbook = workbook.SaveToStream())
{
return streamWorkbook.ToArray();
}
}

to:

public static byte[] ConvertWorkbookToBytes(Workbook workbook)
{
//save the generated workbook to a byte array via a memory stream
MemoryStream streamWorkbook = new MemoryStream();
workbook.Save(streamWorkbook, SaveFormat.Xlsm);
return streamWorkbook.ToArray();
}

I have tested and it works fine. The macro is executed fine in the output file.
For your information, Workbook.SaveToStream() is an older method used to save an XLS file (Excel 97-2003) to streams. So, you should use appropriate Workbook.Save() method specifying the SaveFormat enum.

Thank you.
7

Perfect. Thanks

8

Hi,

Thanks for using Aspose.Cells.

It’s good to know that your issue is resolved by saving to stream directly.

The old method was breaking the xlsm macros which should be fixed also.

If you have any other questions, please feel free to post, we will be glad to assist you.