Add new Module to Worksheet VbaProject using C#.NET

Aspose.Cells Product Family
1

Hi,

Is it possible to add a new module to the vbaProject.Modules collection? I would like to do so programmatically, but I don’t see how - as the VbaModule object does not have a constructor, and there is no .Add method on the workbook.VbaProject.Modules collection.

Thanks

Daniel
2

Hi,


Thanks for providing us some details.

Well, the feature may not be supported to add new modules at the moment although you may modify the code segment of existing Vba Modules. We will check if we can plan the feature, we might log a ticket for it.

We will get back to you soon.

Thank you.
3

Thank you please let me know - this feature is very important to us - and I am sure many users who want to manipulate VBA code will also want to be able to add new modules,


Thanks

DW
4

Hi,


We have logged a ticket with an id “CELLSNET-43596” for your requested feature into our database. This is a complex feature to be supported and we will support later on.

Once we have any update on it, we will let you know here.

Thank you.
5

Hi,


To update you on your logged ticket “CELLSNET-43596”, we have supported your requested feature and hopefully we will provide you fix before the end of this week or so.

Keep in touch.

Thank you.
6

Thank you - I look forward to it!


Daniel
7

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.4.1.2 and let us know your feedback.

8

Thanks! I see the Modules.Add method, but I don’t understand how to use it. Can you provide documentation or a simple example how to use it to copy the code from a worksheet and duplicate it, or copy a module and add it as a new module?

9

Code samples appreciated. I call Modules.Add adding a worksheet or a moduletype and name, and keep getting errors when saving the workbook - that there are duplicate keys - Sheet11 was already present for example.

10

First I add the worksheet via AddCopy, then I try and add the worksheet to the modules collection. How should I be doing this, to add a copy of a worksheet, and then add a module for the new worksheet, based on the old worksheet’s module>?

11

Hi,

Thanks for using Aspose.Cells.

I tried the following code and it worked fine. I have attached the output xlsm file for your reference.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


workbook.VbaProject.Modules.Add(worksheet);


workbook.Save(“output.xlsm”);


12

I think I see at least part of the problem - when I change the name of the module, there is still a non-public property that keeps the old name, Sheet 11, so there will be multiples of that I guess. Can you provide a code snippet that works?

13

Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us your code and source Excel file so that we could look into this issue further and update you.

14

I will try to get a sample together by tomorrow. In the meantime, if you:


1) Take a workbook that has 2 worksheets (Make sure the second one has vba code)
2) AddCopy the second worksheet into the workbook and rename it.
3) Add the new worksheet to the VbaProject.Modules via Modules.Add(worksheet). Rename it by setting the “Name” of the module: (wb.VbaProject.Modules[wb.VbaProject.Modules.Count-1].Name = “newNameString”:wink:
4) AddCopy the second worksheet AGAIN into the workbook and rename it.
5) Add this new worksheet to the VbaProject.Modules via Modules.Add(worksheet). Rename it by setting the “Name” of the module: (wb.VbaProject.Modules[wb.VbaProject.Modules.Count-1].Name = “newNameString2”:wink:

When saving the workbook it errors. The key is already present. And if, in step 5 you put a breakpoint and view the module in the watch window, you will see a non-Public property that has no name, that still says the original name of the original module.

Thanks

DW
15

Hi,

Thanks for your steps and using Aspose.Cells.

We were able to replicate the exception by following your steps using the source xlsm file attached by me with this post and the following sample code.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-43614 - Adding module causes duplicate keys exception on saving workbook

C#

string filePath = @“F:\Shak-Data-RW\Downloads\Book1.xlsm”;


Workbook workbook = new Workbook(filePath);


int idx;

Worksheet worksheet=null;

VbaModule mod = null;


//Set One

idx = workbook.Worksheets.AddCopy(1);


worksheet = workbook.Worksheets[idx];


idx = workbook.VbaProject.Modules.Add(worksheet);


mod = workbook.VbaProject.Modules[idx];

mod.Name = “newNameString”;


//Set Two

idx = workbook.Worksheets.AddCopy(1);


worksheet = workbook.Worksheets[idx];


idx = workbook.VbaProject.Modules.Add(worksheet);


mod = workbook.VbaProject.Modules[idx];

mod.Name = “newNameString2”;


//This will throw exception

workbook.Save(“output.xlsm”);

Exception:
at Aspose.Cells.Workbook.Save(String fileName, SaveOptions saveOptions)
at Aspose.Cells.Workbook.Save(String fileName)

Additional information: Item has already been added. Key in dictionary: 'Sheet3' Key being added: 'Sheet3'

16

Thanks Shakeel! As I mentioned earlier, I found that though the Name property of the new module is correctly changed, there is a non-public member (you can see it if you view the module in the watch window) that maintains the “Sheet3” designation. Perhaps this is the problem.


I eagerly look forward to a fix - thanks so much for all your help and responsiveness!
17

Hi,

Thanks for your elaboration and using Aspose.Cells.

We understands this issue now and hopefully this issue will be fixed soon. Once, there is some news for you, we will let you know asap.

18

Hi Shakeel! Any update on this issue?


Thanks!

Daniel
19

Hi,


Thanks for your inquiry.

To update you on your issue logged as “CELLSNET-43614”, it is fixed. We will provide you the fix after some extensive testing and incorporating other enhancements and fixes. Hopefully, we will provide you the fixed version before the end of this week or so.

Thank you.
20

This is great news - we look forward to the updated DLL. Thanks!


Daniel