Working around VSTO SE's lack of document-customization for Excel 2007
This post was originally published at http://solepano.blogspot.com
I've been doing some research on Visual Studio Tools for Office (VSTO) and Excel 2007 recently and found out that some very interesting features available in previous versions of VSTO for Excel 2003 are not available for Excel 2007. Here's a summary of the current situation and the workarounds I figured out.
State of the Art
VSTO has basically two kinds of Excel add-ins: document-level customization an application-level add-ins.
The document-level customization lets you customize an Excel workbook and also reference the Application to add buttons to the command bar and so forth. The resulting add-in will be only available to the customized workbook. The great benefit of document-level customization is that you can add controls to the spreadsheets within Visual Studio, being these controls part of the VSTO object model. The VSTO objects are wrappers around the Interop ones and they add very interesting functionality, like events and databinding. The VSTO controls are in the Microsoft.Office.Tools.Excel namespace whereas the Interop ones are in Microsoft.Office.Interop.Excel.
Document-level customization project template
Document-level customization project
On the other and, an application-level add-in is, as its name implies, always visible for the host Application regardless of the opened workbook. The only class added by Visual Studio is the TheAddin one, no workbook or worksheet editors are provided. The VSTO controls are not available neither. All Excel objects that we can get access through TheAddin class are from the Microsoft.Office.Interop.Excel API.
Application-level add-in project template
Application-level add-in project
The problem is that, whereas document-level customization is available for Excel2003, is not yet available for Excel 2007. So, the spreadsheet editor is not available within Visual Studio and the VSTO extended controls cannot be used. The table below shows which kinds of add-in are available for each product combination (see Features available by product combination for the complete table listing all Office applications).
| VSTO 2005 or Visual Studio Team System | VSTO 2005 SE installed with VSTO 2005 or Visual Studio Team System | VSTO 2005 SE installed with Visual Studio 2005 Professional Edition | |
| Document-level customizations | Excel 2003 | Excel 2003 | Not available |
| Application-level add-ins | Not available | Excel 2003 Excel 2007 | Excel 2003 Excel 2007 |
Workarounds
In order to make a document-level customization (or at least something similar) to target Excel 2007 I found out that some of the following things can be done:
1- Develop for Excel 2003 and deploy in Excel 2007
It is possible to develop a document-level customization in Visual Studio for Excel 2003 using VSTO 2005. The resulting customized document can then be opened in Excel 2007 in compatibility mode with the same functionality. The drawbacks of this approach is that Excel 2003 needs to be installed in the developer's machine and that you won't have Office 2007 features like custom panes and ribbon extensibility available.
2- Develop for Excel 2007 with VSTO SE and try to emulate the document-level customization.
In order to target Excel 2007, VSTO SE is needed and the only option by the moment is to create an application level add-in. For emulating the document customization you can create an Excel's template file within Excel application, add the desired named ranges, tables, graphics, etc...and then access these controls trough the add-in's code to make custom automation. The drawback of this approach is that the extended functionality of VSTO's controls is not available. For example, you won't be able to use the ListObject databinding support. In order to access the workbook controls, the Interop API must be used. Another drawback, is that the Interop API is not as well documented as the VSTO's.
3- Develop an application-level add-in for Excel 2007 and create the VSTO's objects from code.
Actually I'm not sure wheter this is really a choice, since I couldn't make it work. What I tried here was to create a new instance of a VSTO worksheet to wrap an Interop worksheet. In ThisAddin class I have the following code:
using Excel = Microsoft.Office.Interop.Excel;
using Vsto = Microsoft.Office.Tools.Excel;
namespace ExcelAddIn1
{
public partial class ThisAddIn
{
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
#region VSTO generated code
this.Application = (Excel.Application)Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(typeof(Excel.Application), this.Application);
#endregion
try
{
Excel.Worksheet newSheet = (Excel.Worksheet)this.Application.ActiveWorkbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Tools.Excel.Worksheet extendedSheet = GetExtendedWorksheet(newSheet);
extendedSheet.Controls.AddNamedRange(extendedSheet.Range["A1","C2"], "NamedRange");
}
catch (Exception ex) {
MessageBox.Show(ex.ToString());
}
}
private Vsto.Worksheet GetExtendedWorksheet(Excel.Worksheet nativeWorksheet)
{
//Get the IHostItemProvider instance.
Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider hostItemProvider = (Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider)
RuntimeCallback.GetService(typeof(Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider));
//Create the new worksheet and return it to calling function.
return new Vsto.Worksheet(hostItemProvider, RuntimeCallback, nativeWorksheet.CodeName,
null, nativeWorksheet.Name);
}
But, when trying to add the NamedRange to the extended worksheet this Exception is thrown:
This document might not function as expected because the following control is missing: Sheet4. Data that relies on this control will not be automatically displayed or updated, and other custom functionality will not be available. Contact your administrator or the author of this document for further assistance.
at Microsoft.Office.Tools.Excel.Worksheet.GetObjects()
at Microsoft.Office.Tools.Excel.Worksheet.GetPrimaryControl()
at Microsoft.Office.Tools.Excel.Worksheet.get_Range()
at ExcelAddIn1.ThisAddIn.ThisAddIn_Startup(Object sender, EventArgs e) in C:\...\Projects\ExcelAddIn1\ExcelAddIn1\ThisAddIn.cs:line 26
Conclusion
Although VSTO's document-level customization is not available for Excel 2007 yet, there still are some choices for developing add-ins targeting this application version. Besides, the good news is that this lack will be supplied soon. VSTO "Orcas" will provide document-level customization for Excel 2007 within its new features. So, anyway, it's just a matter of time...