Excel's Extended Worksheet from Native Worksheet

The Setup
I recently had a request to create a simple way (user's perspective) to access and modify a SQL serer table directly without having to connect via the SQL Server Manager and navigating to the Database > Table > Edit.  After giving it some thought I decided to use VSTO to create an Excel interface to the three tables we needed to connect to.


This turned out to be an excellent choice.  90% of this project was simply "plumbing".  Hooking up the TableAdapter to the tables, generating a couple Get methods, an Update and a Delete method.  Finally implementing the SelectionChange event to determine when to update the record.


The hang-up came when I attempted to make use of the Ribbon Designer to provide a couple of user-interface options: one to switch from test to production systems and another to delete the selected record.  The system change was fairly simple once I learned how to export the Ribbon XML and implement the Callback method for a RibbonComboBox control:
  public void comboSystem_TextChanged(Office.IRibbonControl control, string text)

For a full list of prototypes for various Ribbon control events see the list at MSDN here.

The real problem was with the Delete routine.  With three different worksheets that could potentially have been active I needed some way to determine on which table to call the DELETE statement.  I started by creating a C# Interface by which my VSTO Worksheets must abide: ITableSheet that contained just two methods (RefreshData and DeleteRecord) and implemented them appropriately for each of the three Worksheets.  I thought that if I could access the ActiveSheet from the RibbonButton click event, then of course I could simply cast it to an ITableSheet and call DeleteRecord.  This provides an easily scalable way of adding new tables later, should that be necessary.

The Problem

Here's where I learned the difference between the native worksheet (Microsoft.Office.Interop.Excel.Worksheet) and the extended worksheet (Microsoft.Office.Tools.Excel.Worksheet).  It so happens that the ActiveSheet property is a native Worksheet object.  This is not easily cast to a VSTO (extended) Worksheet object.

And here's where the solution gets a little messy.

VSTO 2005: there's an alleged "unsupported" way to make this conversion.  That is to add a method called "GetExtendedWorksheet" to your project.  You can see a discussion on the topic here.  And you can find an example use of it here.

VSTO 2008: Unfortunately, Microsoft prevented this method by removing access to the RuntimeCallback object in VSTO 2008.  This is the version of VSTO that I was using.  A short discussion can be found here.

The Solution (for now)
The work-around that I implemented was to create an ActiveTableSheet property in my ThisWorkbook of type ITableSheet.  When a new worksheet becomes active I manually set the ActiveTableSheet.  Then during the RibbonButton click event, I can access Globals.ThisWorkbook.ActiveTableSheet.DeleteRecord.


The Real Solution (VS 2010)
The good news is that Microsoft appears to be providing a fully supported method of converting the native worksheet to an extended worksheet in VS 2010.  (This is beta and is subject to change.)