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)
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.)