Shaun Logan

background

I have an Excel doc-level solution developed for Office 2003 using VS 2005 Team Suite (with VSTO 2005 SE applied). My requirements are that at runtime, the end-user can insert as many worksheets as s/he wishes into the customized workbook. My doc-level code creates Microsoft.Office.Tools.Excel.Worksheet object host item wrappers around those worksheets -- so that I can programatically add button controls via Microsoft.Office.Tools.Excel.Worksheet.Controls.AddButton().

I did this modeled after code from Carter & Lippert "Visual Studio Tools for Office", p558-560, eg.

Code Block

using Tools=Microsoft.Office.Tools.Excel;

using VstoRT = Microsoft.VisualStudio.Tools.Applications.Runtime;

. . .

public static Tools.Worksheet CreateExtendedWorksheet (

Excel.Worksheet nativeWorksheet, VstoRT.IRuntimeServiceProvider runtimeCallback)

{

VstoRT.IHostItemProvider hip =

runtimeCallback.GetService (typeof (VstoRT.IHostItemProvider)) as VstoRT.IHostItemProvider;

Excel.Workbook workbook = nativeWorksheet.Parent as Excel.Workbook;

workbook.VBProject.VBComponents.Item (1);

// Create the new worksheet and return it to calling function.

return new Tools.Worksheet (hip

, runtimeCallback

, nativeWorksheet.CodeName

, null // Container

, nativeWorksheet.Name

);

// later ...

Excel.Worksheet nativeWorksheet = ... //get this from workbook.ActiveSheet or someplace

Excel.Range range = ... // create a range

Tools.Worksheet toolsWorksheet = CreateExtendedWorksheet(nativeWorksheet,

this.RuntimeCallback);

toolsWorksheet.Controls.AddButton(range, "my button");

This is all working as expected in the target enviroment (Excel 2003 Professional, VSTO 2005 SE runtime).

question

Now I am exploring the feasibility of moving the solution to VSTO 2008 (aka Orcas). What I would like to do is to create a doc-level customization for Excel 2007 using VS 2008 Beta 2, VSTO 2008, and support the same kind of behavior (adding buttons to a worksheet at runtime). My existing source code won't compile in that environment because for one thing, Microsoft.Office.Tools.Excel.Worksheet no longer supports a constructor that takes the IHostItemProvider, IRuntimeCallback, ... Also, Microsoft.Office.Tools.Excel.Workbook no longer exposes the RuntimeCallback member.

So after that long preamble, my question is: how can I achieve the same functionality (creating Worksheet host items/adding buttons to a worksheet at runtime) in a doc-level customization in VSTO 2008/Excel 2007

Thanks for any suggestions; my apologies if there is a more appropriate venue for this question or if there is something obvious I've missed.




Re: Visual Studio Tools for Office 2008 (Pre-release) VSTO 2008, Office 2007: Dynamically creating Excel worksheet host items / adding buttons

Daniel Molina - MSFT

Hi Scoober,

Dynamic customization of worksheets is something that was not officially supported in VSTO 2005 (even though it was possible). Sadly, changes required in the design of our classes for v3 now prevent that old hack to work anymore.

-Daniel






Re: Visual Studio Tools for Office 2008 (Pre-release) VSTO 2008, Office 2007: Dynamically creating Excel worksheet host items / adding buttons

Scoober

Thanks Daniel, for the quick reply. So can I conclude from this that there is no way to programatically add buttons to a worksheet at runtime when running an app-level solution (add-in)

Thanks.






Re: Visual Studio Tools for Office 2008 (Pre-release) VSTO 2008, Office 2007: Dynamically creating Excel worksheet host items / adding buttons

Daniel Molina - MSFT

Just to make it clear, you cannot programatically add buttons at runtime to a non-customized document/worksheet. If they are customized is just a matter of using the {Worksheet||Document}ControlCollection, but thenI guess you would no need the add-in to this.






Re: Visual Studio Tools for Office 2008 (Pre-release) VSTO 2008, Office 2007: Dynamically creating Excel worksheet host items / adding buttons

Scoober

Thanks for the clarification. Yes, I did mean non-customized worksheets.




Re: Visual Studio Tools for Office 2008 (Pre-release) VSTO 2008, Office 2007: Dynamically creating Excel worksheet host items / adding buttons

satpp

Will dynamic customization of worksheets ever be officially supported

Like Scoober, I also allow the user to insert any number of new worksheets using the Carter & Lippert code snippet (though I took it a bit further and derived a class from Microsoft.Office.Tools.Excel.Worksheet). The feature would be nice to have and if it is no longer possible in 2008 it might block us from being able to upgrade to 2008.




Re: Visual Studio Tools for Office 2008 (Pre-release) VSTO 2008, Office 2007: Dynamically creating Excel worksheet host items / adding buttons

Scoober

I obviously cannot comment on the official support question.

However I can share the following:

We have tried a couple of different approaches that both seem to work in VSTO 2008 for a doc-level solution:

1. Call the protected method InitializeControl() in the ctor of the derived class

2. Skip the subclass and just do some coercion to call InitializeControl in a static method that creates the extended/hostitem worksheet. Something along these lines - no warranties expressed or implied ...

Code Block

Tools.Worksheet hostItemSheet = new Tools.Worksheet (cookie, id);

ISupportInitializeControl initControl = hostItemSheet as

ISupportInitializeControl;

initControl.InitializeControl (itemProvider, hostContext, cookie, null,id);

Both approaches seems to work fine in VSTO 2008 beta 2, using a doc-level solution (ThisWorkbook). No luck yet getting either approach to work in VSTO 2008 for an app-level addin (ThisAddin). This issue is blocking us from moving to VSTO 2008 for an app-level addin.






Re: Visual Studio Tools for Office 2008 (Pre-release) VSTO 2008, Office 2007: Dynamically creating Excel worksheet host items / adding buttons

siamsimon

Hi Daniel,

I am intersted by the method for customization of worksheets in VSTO 2005.

How i can make this the
customization of worksheets in VSTO 2005

Thank you

Siam.