VSTO Programmatically adding a new worksheet based on an existing one.

Question

Hi All,

I currently have a C# Workbook-level Office 2007 Addin that has a Worksheet added at design-time where I have added methods and properties to the Worksheet derived class.

I want to be able to programatically make a new worksheet that clones, inherits or otherwise possesses the methods and event handlers of this existing Worksheet class automatically.

If this can be achieved then please could someone outline how this could be achieved? C# demo code would be preferably although any .NET code would be acceptable.

Thank-you for your time.

Answer 1

Ok, good question, and I'll be keeping an eye on what comes up on here.

A few months ago, I had a similar problem, and needed to create code and events on dynamically created sheets. To be able to do that, however, I ended up creating VBA script objects within the sheet.

So the process was:

1. User Clicks button in Excel
2. C# process creates new Sheet
3. C# process creates VBA object on sheet and creates events, etc

     oBook = objExcel.ActiveWorkbook;

    oModule = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);

    string sCode = "sub myVBASub()\r\n"+
         "msgbox("Hello")\r\n"+
         "End Sub\r\n";

    oModule.CodeModule.AddFromString(sCode);

Answer 2

I've now come to the conclusion that it's not actually feasible to duplicate these classes in their existing state as I was looking for.

According to the Host Items and Host Controls Overview: in Document Level Addins, Host Items cannot be created programmatically but only at design time. This is reinforced in the further explanation of the programmatic limitations of Host items and host controls, particularly for document level addins.