how to copy ExcelWorkSheet from template with new CodeModule

Question

I am having an issue with copying a ExcelWorkSheet and the corresponding CodeModule of the copied sheet. Let me explain:

Scenario: I have an excel file with macro's (.xlsm) and I use this excel file as a template to create an other excel file (data will be copied into the new file).

So, first I fetch some data from a database and open the excel template (.xlsm) file.

At a certain point, some entries need to be in different worksheets, this is where the 'WorkSheets.Add()' comes in.

 var newSheet = workbook.Worksheets.Add("someName", templateSheet);

After copying the template sheet (which contains 'VBA' code that I want to duplicate as well), I am experiencing problems.

It seems that the 'CodeModule' of the newly created sheet equals the one from the template by name AND by reference.

That is; all the property values are the same and: workbook.VbaProject.Modules contains only the initial code modules of the template file and not a new one for the new newSheet.

Even worse, if I want to bind a new CodeModule like this:

workbook.VbaProject.Modules.AddModule("test");
newSheet.CodeModule.Name = "test";

both the newSheet.CodeModule and templateSheet.CodeModule are set to null (well Nothing actually, since I am using VB.Net).

So the question: is this a bug or am I doing something wrong here? And even better: can you guide the way to accomplish this scenario?

Answer 1

It seems to be a bug. I have created an issue in the issue center of the epplus project. It has been marked as resolved

https://epplus.codeplex.com/workitem/15095

I haven't tested the new version yet. I'll give an update when that's done.

Update In the latest sources this issue is resolved.