Jerry Nixon on Windows: Use VSTO 3 to add SmartTags to Excel 2007

Jerry Nixon on Windows

Wednesday, November 19, 2008

Use VSTO 3 to add SmartTags to Excel 2007

SmartTags are simplified in VSTO 3 Service Pack 1. You get VSTO 3 Service Pack 1 when you download Visual Studio 2008 Service Pack 1 from Microsoft. Google for it. Once you have that, you can add a SmartTag very easily.

A SmartTag appears adjacent to cells when the content of the cell is recognized. Built-in Office SmartTags recognize things like Stock Symbols (see screen shot below) and Contact Names found in Outlook . Your SmartTag will need to be smart to identify values correctly.

image

To recognize content you add one, two, or one hundred terms. You can also point to a list. Or, you can use regular expressions to recognize string patterns. All are useful. You can mix them if you want. My demo uses regular expressions.

Remember, the purpose of a SmartTag is to let users do something. If you want a SmartTag, first you need one or more "Actions" which translate items in the menu users see when clicking on your tag (like "Stock quote on MSN MoneyCentral" shown above).

The sample below assumes all numbers in a Workbook refer to a record ID in the Northwind Employees table. Is this the type of SmartTag you want in your application? I hope not. But I think it gets the point across.

// smart tag actions

Microsoft.Office.Tools.Excel.Action _Action1;
_Action1 = new Microsoft.Office.Tools.Excel.Action(
"Lookup HR Record");
_Action1.Click += delegate(object xsender,
    Microsoft.Office.Tools.Excel.ActionEventArgs xe)
{
// do something;
};

Microsoft.Office.Tools.Excel.Action _Action2;
_Action2 = new Microsoft.Office.Tools.Excel.Action(
"Research Sales Orders");
_Action2.Click += delegate(object xsender,
    Microsoft.Office.Tools.Excel.ActionEventArgs xe)
{
// do something;
};

Microsoft.Office.Tools.Excel.Action _Action3;
_Action3 = new Microsoft.Office.Tools.Excel.Action(
"Terminate Employee");
_Action3.Click += delegate(object xsender,
    Microsoft.Office.Tools.Excel.ActionEventArgs xe)
{
// do something;
};

// smart tag

Microsoft.Office.Tools.Excel.SmartTag _SmartTag;
_SmartTag = new Microsoft.Office.Tools.Excel.SmartTag(
"jerry#demo", "SalesPerson Details");
_SmartTag.Expressions.Add(
new System.Text.RegularExpressions.Regex(@"\d"));
_SmartTag.Actions =
new Microsoft.Office.Tools.Excel.Action[]
{ _Action1, _Action2, _Action3 };

// add to workbook

Globals.ThisWorkbook.SmartTagOptions.EmbedSmartTags = true;
Globals.ThisWorkbook.VstoSmartTags.Add(_SmartTag);