On the personal VSTO project I currently work on, I was first working on sheets I had added at design time. I was thinking it would be simpler to be able to generate the worksheets at runtime depending of my needs.
To add a sheet, we can just do something like :
Globals.ThisWorkbook.Worksheets.Add(System.Type.Missing, System.Type.Missing, 1, XlSheetType.xlWorksheet);
However we can note the following things
-
The worksheets added at design time are Microsoft.Office.Tools.Excel.Worksheet
-
The worksheets added at runtime are Microsoft.Office.Interop.Excel.Worksheet
-
The add method :
-
expects only optional arguments
-
its two first arguments (Before and After) must be of type Interop.Excel.Worksheet
-
it will return an Interop.Excel.Worksheet
-
As far as I have seen - after googling quite a lot - there is no way to convert a Tools.Excel.Worksheet to a Interop.Excel.Worksheet
However there are some way (not given or advised by the MSDN) to convert a Interop.Excel.Worksheet to a Tools.Excel.Worksheet (search GetExtendedWorksheet in google).
Having these two types and no built in conversion is driving me crazy... I think for now I will continue working with sheets at design time. If you have any advice, leave a comment !