Macros lost when combining/copying workbooks

Aspose.Cells Product Family
1

Hi,

When I tried to combine/copy two workbooks, I noticed a weird issue causing macro missing.

Code:

var workbook1 = new Workbook(@".\test1.xlsm");
var workbook2 = new Workbook(@".\test2.xlsx");
// Console.Write(workbook1.VbaProject.Modules.Count);
workbook1.Copy(workbook2);
workbook1.Save(@".\out.xlsm");

workbook1 had a macro but in the output, it is missing.

What is weird is that if I tried to access the VbaProject property (for example by uncommenting the line in my code sample) before copying, then the output will have the macro properly.

Reading/accessing a member shouldn’t affect the output.

I’ve attached my test files for your reference. Could you please help us take a look?

missingmacro.zip (28.7 KB)

Thanks,

2

@ServerSide527,
I have tried the scenario using Aspose.Cells for .NET 20.8.x but could not observe any issue as macro is available in the output file. Could you please test the scenario using the latest version and share the feedback.

Following is the code which is tested:

var workbook1 = new Workbook(path + @"test1.xlsm");
var workbook2 = new Workbook(path + @"test2.xlsx");
Console.WriteLine(workbook1.VbaProject.Modules.Count);
workbook1.Copy(workbook2);
Console.WriteLine(workbook1.VbaProject.Modules.Count);
workbook1.Save(path + @"out.xlsm");
Console.WriteLine(workbook1.VbaProject.Modules.Count);
var workbook3 = new Workbook(path + @"out.xlsm");
Console.WriteLine(workbook3.VbaProject.Modules.Count);

Here is the program output:
1
1
1
1

Here is the output file:
out.zip (8.0 KB)

3

Hi,

I was using the latest Aspose.Cells 20.8.0 to replicate the issue.

Please run the exact code I provided above. What I meant is that if I do not read the VbaProject member property, the macro will be missing in the final output.

var workbook1 = new Workbook(@".\test1.xlsm");
var workbook2 = new Workbook(@".\test2.xlsx");
workbook1.Copy(workbook2);
workbook1.Save(@".\out.xlsm");

Once you attempt to access workbook1.VbaProject.Modules.Count, the issue is gone. So you need to replicate the issue without all the Console.WriteLine() code.

Added an example screenshot for your reference. You can see the only difference between the two cases was that whether I tried to print the property before copying, resulting in completely different results after the copy.

image.png (26.0 KB)

Thanks

4

@ServerSide527,
We have observed the issue and logged it in our database for further investigation. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSNET-47570-Macros lost when combining/copying workbooks

5

@ServerSide527,
If we call workbook1.Copy(workbook2), we have to remove all data (including Macors) in the workbook, but the Macros are not removed after

Console.WriteLine(workbook1.VbaProject.Modules.Count);//NOTE THIS LINE.

We will fix this issue soon.

6

[Delete] Since Aspose mimics Office behaviour, I believe the expected behaviour should be the other way around -> Macros in workbook1 (the destination workbook) should not be removed, because when you copy data from workbook2 to workbook1 manually using MS Excel, the macro in workbook1 will not be removed. [/Delete]

Actually I have checked further and I don’t think there’s an equivalent of Copying data in Excel, so Aspose behaviour makes sense here, please ignore the comment above.

However, we will need a way from Aspose to manually keep/copy the macros, as we previously relied on the .Copy() method but now it seems that I will have to change that as it was not expected in the first place. Could you point out a way to keep the macros in the destination workbook?

7

@ServerSide527,
We have noted your concern and discussing it here. We will write back here once some feedback is ready to share.

8

@ServerSide527,

We will add an overload method, i.e., Copy(Workbook source,CopyOptions copyOptions) and CopyOptions.KeepMacros to support your need. See following sample code:
e.g
Sample code:

var workbook1 = new Workbook(dir + ` "test1.xlsm");
Console.WriteLine(workbook1.VbaProject.Modules.Count);
var workbook2 = new Workbook(dir + @"test2.xlsx");
CopyOptions options = new CopyOptions();
options.KeepMacros = true;
workbook1.Copy(workbook2, options);
Console.WriteLine(workbook1.VbaProject.Modules.Count);
workbook1.Save(dir + "dest.xlsm");
9

Great, thanks for your updates!

I have checked further on Aspose APIs, according to Copy workbook preserving macros - #2 by amjad.sahi, when you run workbook1.Copy(workbook2), workbook2 (the source file) macro will be preserved.

I’m wondering with this new flag set to true, what will be the behaviour, will it be:

  1. the macro of the source file will be removed and macro of the destination will be preserved, or
  2. the macros of both file will be combined?

Thanks,

10

@ServerSide527,
Please try our latest version/fix: Aspose.Cells for .NET v20.8.5:

Aspose.Cells20.8.5 For .Net4.0.Zip (5.4 MB)
Aspose.Cells20.8.5 For .Net2_AuthenticodeSigned.Zip (5.4 MB)

Please try the fix with the following codes and then let us know your feedback.

CopyOptions options = new CopyOptions();
options.KeepMacros = true;
workbook1.Copy(workbook2, options);
11

Thank you for the quick updates!

I have checked the following scenario with workbook1.Copy(workbook2, options):

When workbook1 has macros, workbook2 doesn’t have macros:

KeepMacros is false => workbook1 has no macro after copy
KeepMacros is true => workbook1 has macros from the original workbook1

When workbook1 has macros, workbook2 also has macros:

KeepMacros is false => workbook1 has macros from the original workbook2, macros in workbook1 are removed
KeepMacros is true => workbook1 has macros from the original workbook2, macros in workbook1 are removed

Therefore I understood that KeepMacro will not take effect if the source workbook already has macros.

This is ok for my business need, but could you confirm if that is the designated behaviour?

Thanks,

12

@ServerSide527,
Your understanding seems correct but we will confirm you the behavior of this new overload.

13

@ServerSide527,
Yes. KeepMacro will not take effect if the source workbook already has macros.

1 Like
14

The issues you have found earlier (filed as CELLSNET-47570) have been fixed in Aspose.Cells for .NET v20.9. This message was posted using Bugs notification tool by Amjad_Sahi
You may also get the new version @ nuget repos. (NuGet Gallery | Aspose.Cells 20.9.0)