Use Smart Tags to Enable Data Drill-Down in Excel

Article
07/11/2006

John R. Durant
Microsoft Corporation

August 2003

Applies to:
Microsoft® Office Excel 2003
Microsoft® Excel 2002

Summary: Learn how to use smart tags to reestablish the connection between data stored in spreadsheets with other data in your enterprise. Discover how to develop smart tag recognition using regular expressions. Finally, learn how to use the Excel user interface as a convenient mechanism for drilling down into data. Populate multiple sheets with data based on smart tag actions. (20 printed pages)

Business Scenario
Creating the Project
Smart Tag Recognition
Smart Tag Actions
Deploying the Smart Tag Solution
Conclusion

Business Scenario

Drilling down into increasingly specific data is a common user task, and developers must regularly create user interfaces for customers to get to the data they need. However, while circumstances sometimes require custom user interfaces, an Office application such as Microsoft® Office Excel 2003 can often suffice. You can use smart tags to create a user interface that lets users drill into data progressively. For example, a user sees a list of customers, chooses a customer, and views a list of orders. From there, the user can select an order to view its detail. Smart tags make this type of user interface possible with in the familiar Excel spreadsheet environment to which users are accustomed.

Smart tags can provide another benefit at the same time by reconnecting data in spreadsheets to database data as users seek information that is more detailed. The fact is that most companies populate many spreadsheets with data for one time use and then leave the spreadsheets static, losing the connection to other information that could make the spreadsheets more useful and meaningful and reusable. For example, take a look at the spreadsheet in Figure 1 which shows aggregate sales totals for different customers by date.

Figure 1. Spreadsheet without smart tag support

This spreadsheet, while useful on its own, could be more useful if the user could quickly access related information based on what this spreadsheet displays. That information could include:

Customer contact and address information
Order summary information
Order history details

Notice the differences with the spreadsheet shown in Figure 2, which takes advantage of smart tags to give access to a broader scope of information.

Figure 2. Spreadsheet with smart tags enabled

A smart tag marks up each customer's identifier in the spreadsheet, and the actions for customers include looking at the customer's basic information and viewing its order history. Used in this way, smart tags breathe new life into spreadsheets whose data previously may not have included a connection to related source data. Additionally, each smart tag action lets the user view more detailed corporate data, opening limitless activities that ultimately parallel the functions of a business and how its employees work with company data.

**Note **This project assumes familiarity with smart tag development and deployment. If you are unacquainted with smart tags and how they work, you should read the following material before attempting to complete the solution in this article:

Creating the Project

A single DLL created with Microsoft Visual Basic® 6.0 allows you to enable the smart tag functionality.

To create and configure the DLL using Microsoft Visual Basic 6.0

In Visual Basic 6.0, on the File menu, click New Project.
In the New Project pane, click ActiveX DLL, and then click Open.
If Visual Basic added any default class modules remove them. To do so, in Project Explorer, right-click the class file and then click Remove ClassName. When prompted whether or not to save changes to the class, click No.
In the Properties window, change its name to CustOrders.
Add a new class module to the project by right-clicking the project in the Project Explorer. Point to Add on the menu, and then click Class Module.
Click Open in the Add Class Module dialog box.
With the class selected, in the Properties window change its name to COActions.
Repeat steps 5-7 to add another class module to the project, this time naming it CORecognizer.
Save the project and its files.

At this point, the basic structure of the smart tag DLL is in place, and you can begin to add code to the solution. So far, you have created a project with two class modules, one for handling smart tag recognition and one for smart tag actions.

**Note **You can place the code for the recognizer and the action provider in the same class module. However, it is customary to use separate classes for recognition and actions to make the project easier to navigate and debug.

Although the basic structure of the project is in place, without properly adding references to other software libraries, your code will not run as a smart tag nor will it be able to interact with Microsoft Excel. There are four main references you must add for your smart tag solution to function properly. They are:

Microsoft Smart Tags 2.0 Type Library
Microsoft ActiveX Data Object 2.7 Library
Microsoft Excel 11.0 Object Library
Microsoft VBScript Regular Expressions 5.0

**Note **These library versions are later ones associated with Microsoft Office System 2003 and current versions of the other libraries. However, adding references to libraries for Office XP work just as well. For example, the code in this article also works with the Smart Tags 1.0 Type Library, Microsoft Excel 10.0 Object Library, and Microsoft ActiveX Data Objects 2.6 Library. Similarly, the Microsoft VBScript Regular Expressions 5.0 type library is also compatible with this code.

To add a reference the libraries

On the Projects menu, click References.
Select or clear the appropriate check boxes to add references to the libraries listed previously (see Figure 3) and then click OK to close the dialog box and return to your project.

Figure 3. Reference libraries in the smart tag project

Before writing custom code for your project, you need to implement that interfaces for smart tag recognition and actions. These interfaces are essential because they allow host applications such as Microsoft Excel to access the methods of your project.

To implement these interfaces

In the General Declarations section of the class module for smart tag recognition, CORecognizer, type the following code and then press Enter:
```
Implements ISmartTagRecognizer
```
**Note **You must then implement each of the members for this interface, even if you do not use a specific member. In other words, the interface must be fully implemented to work properly, even though some members do not receive custom code.
From the Object list at the top of the Code pane select ISmartTagRecognizer.
From the Procedure list at the top of the Code pane, select each procedure in the list. In so doing, Visual Basic adds a code stub for each member in the interface.
In the Project window, double-click the COActions class module.
Repeat these to implement members of the ISmartTagActions interface.

Smart Tag Recognition

Probably the most important aspect of smart tag technology, from a developer perspective, is the notion of a smart tag type. When the host application marks up recognized text with a smart tag, it associates the text with a specific type. The smart tag actions can then perform operations based on that type. For example, if the text 434VINET is recognized as a customer ID, then the smart tag associates this text with the following type: urn:schemas-microsoft-com:office:custorders#customerid. This type is a custom type a developer creates, and the code for smart tag actions then provides a list of actions when a user the Smart Tag Actions button so that it can carry out the selected action from the list.

When the host application starts, it queries the smart tag DLL to obtain information based on the recognizer's behaviors and attributes. The host application identifies the ProgID, name, description, the number of supported smart tag types, the names of those types, and, if applicable, a URL to more information about the smart tag. The host application discovers these bits of information about the recognizer by accessing the recognizer's properties and their values. The implementation of these properties is as follows:

Private Property Get ISmartTagRecognizer_ProgId() As String
    ISmartTagRecognizer_ProgId = "CustOrders.CORecognizer"
End Property
Private Property Get ISmartTagRecognizer_Name( _
  ByVal LocaleID As Long) As String
    ISmartTagRecognizer_Name = "Customer Order Recognizer"
End Property
Private Property Get ISmartTagRecognizer_Desc( _
  ByVal LocaleID As Long) As String
    ISmartTagRecognizer_Desc = "Recognizes customer order information"
End Property
Private Property Get ISmartTagRecognizer_SmartTagCount() As Long
    ISmartTagRecognizer_SmartTagCount = 2
End Property
Private Property Get ISmartTagRecognizer_SmartTagName( _
  ByVal SmartTagID As Long) As String
      Select Case SmartTagID
        Case 1
          ISmartTagRecognizer_SmartTagName = _
            "urn:schemas-microsoft-com:office:custorders#customerid"
        Case 2
          ISmartTagRecognizer_SmartTagName = _
            "urn:schemas-microsoft-com:office:custorders#orderid"
      End Select
End Property
Private Property Get ISmartTagRecognizer_SmartTagDownloadURL( _
  ByVal SmartTagID As Long) As String
    ISmartTagRecognizer_SmartTagDownloadURL = ""
End Property

This recognizer supports two types:

urn:schemas-microsoft-com:office:custorders#customerid
urn:schemas-microsoft-com:office:custorders#orderid

The main job of the recognizer is to determine which Microsoft Excel spreadsheet content should be assigned to the one type or the other, or if it the smart tag should ignore the text altogether. To judiciously sift the text in this manner, the host application repeatedly calls the Recognize method of the recognizer as text is inserted. The custom code in this method must determine which type, if any, with which to associate the text as the following example shows:

Private Sub ISmartTagRecognizer_Recognize(ByVal Text As String, _
  ByVal DataType As SmartTagLib.IF_TYPE, ByVal LocaleID As Long, _
  ByVal RecognizerSite As SmartTagLib.ISmartTagRecognizerSite)
    Dim propbag As ISmartTagProperties
    Text = UCase(Text)
    Dim regEx As VBScript_RegExp_55.RegExp
    Dim oMatch As Match
    Dim oMatches As MatchCollection
    Set regEx = New VBScript_RegExp_55.RegExp
    regEx.Pattern = "\d{3}[A-Z]{5}"
    Set oMatches = regEx.Execute(Text)
    If oMatches.Count > 0 Then
      Set oMatch = oMatches(0)
      If Len(Text) = Len(oMatch.Value) Then
        Set propbag = RecognizerSite.GetNewPropertyBag()
        propbag.Write "ID", oMatch.Value
        RecognizerSite.CommitSmartTag _
        "urn:schemas-microsoft-com:office:custorders#customerid", _
        1, oMatch.Length, propbag
        Exit Sub
      End If
    End If
    regEx.Pattern = "\d{5}-\d{3}[A-Z]{5}"
    Set oMatches = regEx.Execute(Text)
    If oMatches.Count > 0 Then
      Set propbag = RecognizerSite.GetNewPropertyBag()
      propbag.Write "ID", Text
      RecognizerSite.CommitSmartTag _
      "urn:schemas-microsoft-com:office:custorders#orderid", _
      1, Len(Text), propbag
      Exit Sub
    End If
End Sub

You can summarize the code in this method as follows:

Convert any letters in the text to upper case.
Use a regular expression to find out if the text matches a specific pattern. If so, assign a corresponding type to the text. If not, check against another pattern.
Use a regular expression to determine if the text matches a different pattern. If so, assign a corresponding type to the text. If not, do nothing.

This method effectively uses the RegExp object to compare text against a pattern. The first pattern is for three numeric digits followed by five upper case letters between A and Z. The second pattern is for five digits and a hyphen followed by the same pattern just mentioned. The first pattern models customer IDs in the organization. The second pattern models order identifiers. Ultimately, the smart tag actions use these IDs to refer to a database and extract relevant information.

**Note **The data for this solution are modified versions of data in the Northwind database. New tables containing data for customers, orders, and order detail were added with modified unique identifiers to make the regular expression usage more interesting. You can use the built-in table definitions and data for the Northwind database, and the sample still runs as long as you change the regular expression patterns.

When the recognizer finds a suitable match for the textual input, it acquires a new property bag and adds an entry into the property bag for the recognized text. A property bag in smart tag technology is way of storing named value pairs so that both the recognizer and action provider can access the information. Thus, code in the action provider can get to data that recognizer has stored. Our recognizer stores the ID, for either a customer or an order, along with a name ID.

Then, the recognizer commits the smart tag calling the CommitSmartTag method of the RecognizerSite object. The Recognize method passes a reference to the RecognizerSite object as a parameter, so you need not do anything to use this object. The RecognizerSite object helps manage the interface between the Smart Tag DLL and the specific instance in the host application of the text to be assigned the smart tag. In short, it helps maintain the hook between the host application and the smart tag DLL.

Committing the smart tag also means assigning a smart tag type to the text. The type here depends on the pattern to which the text conforms. Once a smart tag is committed, the host application adds its special markup to the text. Microsoft Word underlines the text. Microsoft Excel adds a small symbol to the corner of the text's cell. The host application needs to know the length of the recognized text so that it can mark it up properly, so the CommitSmartTag method has a parameter for the starting character position of the recognized text and another parameter for its length. The final parameter is for the property bag mentioned earlier.

Smart Tag Actions

The primary function of the action provider is to provide users with the option to take action on terms returned by the recognizer and then execute code based on selected action. To do so, the action provider must specify to the host application the smart tag types the action provider supports, the number of types, their names, and how many specific actions each type supports. These specific actions are called verbs in smart tag parlance. Each verb must provide a caption that can appear in the user interface.

The host application identifies the ProgID, name, supported types, and caption of the action provider by accessing properties of the Action class. Much like those of the recognizer class, the implementations are as follows:

Private Property Get ISmartTagAction_ProgId() As String
  ISmartTagAction_ProgId = "CustOrders.COActions"
End Property
Private Property Get ISmartTagAction_Name( _
  ByVal LocaleID As Long) As String
  ISmartTagAction_Name = "Customer and Order Actions"
End Property
Private Property Get ISmartTagAction_Desc( _
  ByVal LocaleID As Long) As String
  ISmartTagAction_Desc = "Actions for customers and orders"
End Property
Private Property Get ISmartTagAction_SmartTagCount() As Long
  ISmartTagAction_SmartTagCount = 2
End Property
Private Property Get ISmartTagAction_SmartTagName( _
  ByVal SmartTagID As Long) As String
  Select Case SmartTagID
    Case 1
      ISmartTagAction_SmartTagName = _
        "urn:schemas-microsoft-com:office:custorders#customerid"
    Case 2
      ISmartTagAction_SmartTagName = _
        "urn:schemas-microsoft-com:office:custorders#orderid"
  End Select
End Property 
Private Property Get ISmartTagAction_SmartTagCaption( _
  ByVal SmartTagID As Long, ByVal LocaleID As Long) As String
  ISmartTagAction_SmartTagCaption = "CustomerOrderActions"
End Property

These properties provide only part of the story. A smart tag action provider has a list of actions available for each type, and those actions appear in the smart tag menu as shown Figure 2. The actions in the menu vary depending on the smart tag type. Thus, whereas Figure 2 shows actions for a recognized customer ID, Figure 4 shows the actions for a recognized order ID.

Figure 4. Actions for a recognized order ID

Smart tags associated with customer IDs provide two actions, or verbs, whereas those associated with order IDs only provide one verb. The mapping between smart tag types and the verbs they provide occurs through the additional properties as shown in the following:

Example

Private Property Get ISmartTagAction_VerbCount(ByVal SmartTagName As String) As Long
  Select Case SmartTagName
    Case "urn:schemas-microsoft-com:office:custorders#customerid"
      ISmartTagAction_VerbCount = 2
    Case "urn:schemas-microsoft-com:office:custorders#orderid"
      ISmartTagAction_VerbCount = 1
  End Select
End Property

The ISmartTagAction_VerbCount property receives a smart tag type name as a parameter and returns the number of verbs that the type supports. Next, the action provider accesses the ISmartTagAction_VerbID property once for each verb associated with a type. This property returns a unique numeric identifier for the verb as shown in the following:

Example

Private Property Get ISmartTagAction_VerbID(ByVal SmartTagName As String, ByVal VerbIndex As Long) As Long
  Select Case SmartTagName
    Case "urn:schemas-microsoft-com:office:custorders#customerid"
      ISmartTagAction_VerbID = VerbIndex + 100
    Case "urn:schemas-microsoft-com:office:custorders#orderid"
      ISmartTagAction_VerbID = VerbIndex + 200
  End Select
End Property

The smart tag type for customer IDs supports two verbs. The action provider calls the ISmartTagAction_VerbID property twice and gives the VerbIndex parameter a value of 1 to indicate the first time, and a value of 2 to indicate the second time. One may wisely wonder why the property should not just return the VerbIndex as the property if, in fact, it increments by one each time it is called. However, after the count is complete for the first type and the two verbs are counted, the count begins anew for the second type. So, as ISmartTagAction_VerbID is called for the first verb associated with second type, the VerbIndex begins again at 1. Thus, the only verbs listed would be those associated with the final smart tag type whose verbs get counted. To distinguish between them, the ISmartTagAction_VerbID property includes logic that adds a number to VerbIndex based on the type that is passed into the property as a parameter. Thus, if the type is urn:schemas-microsoft-com:office:custorders#customerid, then the numeric identifier for all of its verbs is between 101 and 200. Not all of the numbers are used, and it is unlikely that the number of verbs for any given type exceeds several much less a full hundred. Similarly, all verbs for the urn:schemas-microsoft-com:office:custorders#orderid type begin at 201 and count up from there. This numeric identifier is important, because the smart tag action provider uses it to identify which verb is invoked in response to a user's selection. The identifier is also used to acquire a caption for a specific verb through the ISmartTagAction_VerbCaptionFromID property. The smart tag menu displays the caption for the user's benefit in making a selection as shown in the following:

Example

Private Property Get ISmartTagAction_VerbCaptionFromID(ByVal VerbID As Long, ByVal ApplicationName As String,
 ByVal LocaleID As Long) As String
  Select Case VerbID
    Case 101
      ISmartTagAction_VerbCaptionFromID = "Add Customer Info"
    Case 102
      ISmartTagAction_VerbCaptionFromID = "Show Order History"
    Case 201
      ISmartTagAction_VerbCaptionFromID = "Show Order Detail"
  End Select
End Property

Finally, the unique identifier provides another way of accessing a verb. There are times when an action is invoked, not in response to direct user interaction on the menu, but by programmatic access. In other words, while users can click an item on a menu to set the smart tag action in motion, there is a programmatic equivalent of this activity so that custom code can invoke these same actions. The ISmartTagAction_VerbNameFromID property makes this caption accessible:

Example

Private Property Get ISmartTagAction_VerbNameFromID(ByVal VerbID As Long) As String
  Select Case VerbID
  Case 101
    ISmartTagAction_VerbNameFromID = "AddCustomerInfo"
  Case 102
    ISmartTagAction_VerbNameFromID = "ShowOrderDetail"
  Case 201
    ISmartTagAction_VerbNameFromID = "ShowOrderHistory"
  End Select
End Property

At last, the action provider is nearly complete. All that remains is to invoke the verbs and make the action provider perform a task (such as getting more data from a database). The ISmartTagAction_InvokeVerb method serves a pivotal role when a verb is invoked, either through user interaction or through code. The method accepts several parameters, one of which is VerbID. This parameter, not surprisingly, contains the unique identifier for the verb that is invoked. Your InvokeVerb method should contain conditional logic to perform different tasks according to the value of VerbID. Below is a truncated version of this code. The code for each verb is explained later in the paper, but is removed here to clarify the structure of the method:

Example

Public Sub ISmartTagAction_InvokeVerb(ByVal VerbID As Long, _
 ByVal ApplicationName As String, ByVal Target As Object, _
 ByVal Properties As SmartTagLib.ISmartTagProperties, _
 ByVal Text As String, ByVal XML As String)
 Dim cmd As ADODB.Command
 Dim p As ADODB.Parameter
 Dim activeSheet As Excel.Worksheet
 Dim rs As Recordset
 Dim i As Integer
 Dim r As Excel.Range
 If cn.State <> adStateOpen Then
    cn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost; " _
    & "Integrated Security=SSPI;Initial Catalog=Northwind;"
    cn.Open
 End If
 Set cmd = New ADODB.Command
 With cmd
    Set .ActiveConnection = cn
    .CommandType = adCmdStoredProc
 End With
 Select Case VerbID
    Case 101
      'Code for verb 101 goes here
    Case 102
       'Code for verb 102 goes here
    Case 201
      'Code for verb 201 goes here
 End Select
 activeSheet.Activate
End Sub

All of the verbs in this solution call stored procedures in a Microsoft SQL Server database. Those procedures return a Recordset filled with data tailored to the context represented by the smart tag. The first lines of code in the InvokeVerb method set up the connection to the database and prepare the Command object for use.

**Note **You can easily duplicate the stored procedures for this solution by creating them using the names provided in the code for each verb listed here. Each procedure contains a simple SELECT statement with a WHERE clause whose value is set by the parameter in the stored procedure. The parameter names are also listed in the code for each verb. For example, the stored procedure for verb 101, sp_GetCustomerInfo, is:

SELECT * FROM cust WHERE CustomerID =@CustomerID. The cust table is, as previously mentioned, a modified form of the Customers table that appears in the Northwind database. You can use the original table rather than a modified one by altering your stored procedures.

The Command object here uses a Connection object, cn that is declared the General Declarations section of the class module and initialized in the Class_Initialize event of the class as follows:

Example

Private Sub Class_Initialize()
  Set cn = New ADODB.Connection
End Sub

In the InvokeVerb method, if the connection to the database is closed, the code opens the connection:

Example

If cn.State <> adStateOpen Then
    cn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost; " _
    & "Integrated Security=SSPI;Initial Catalog=Northwind;"
    cn.Open
  End If

Then, the code refers to the specific verb actions. When the value of the verb identifier is 101, then the code retrieves detailed customer information from the database and places it in the spreadsheet as follows:

Example    Set activeSheet = Target.Application.Sheets(2)
      activeSheet.Cells.Clear
      With cmd
        .CommandText = "sp_GetCustomerInfo"
        Set p = .CreateParameter("@CustomerID")
        p.Direction = adParamInput
        p.Type = adChar
        p.Size = 8
        p.Value = Properties.ValueFromIndex(0)
        .Parameters.Append p
        Set rs = .Execute()
      End With
      If Not rs.EOF Then
        With activeSheet
          .Range("A1").Value = "Detail for Order: " _
            & Properties.ValueFromIndex(0)
          .Range("A2").Value = rs(1).Name
          .Range("B2").Value = rs(2).Name
          .Range("C2").Value = rs(3).Name
          .Range("A3").Value = rs(1).Value
          .Range("B3").Value = rs(2).Value
          .Range("C3").Value = rs(3).Value
          .Activate
        End With
      End If

First, the code adds a reference to the second sheet in the workbook and clears any data it contains. Then, it sets up the properties of the Command object that calls a stored procedure. This stored procedure accepts a parameter that includes the recognized customer ID. To retrieve this ID value, inspect the property bag that the recognizer populated previously. Recall that the Recognize method contained logic to save a named value pair in the property bag, and it is here that the ID value is saved. The entire property bag is tied to the specific smart tag and passed in to the InvokeVerb method as a parameter called Properties. We can inspect this collection of properties and extract the ID value saved by the recognizer so that we can pass it to the stored procedure. All three verbs use this same technique. After executing the stored procedure and receiving a recordset in return, the code populates the spreadsheet so that it looks like the one in Figure 5.

Figure 5. Results after invoking verb 101

When the verb identifier is 102, then the code retrieves order summaries for the customer form the database and places it in the spreadsheet in the following manner:

      Set activeSheet = Target.Application.Sheets(2)
      activeSheet.Cells.Clear
      With cmd
        .CommandText = "sp_GetOrders"
        Set p = .CreateParameter("@CustomerID")
        p.Direction = adParamInput
        p.Type = adChar
        p.Size = 8
        p.Value = Properties.ValueFromIndex(0)
        .Parameters.Append p
        Set rs = .Execute()
      End With
      If Not rs.EOF Then
        activeSheet.Range("A1").Value = "Orders for Customer " _
          & Properties.ValueFromIndex(0)
        With activeSheet
          .Range("A2").Value = rs(0).Name
          .Range("B2").Value = rs(2).Name
          .Range("C2").Value = rs(3).Name
          .Range("D2").Value = rs(4).Name
        End With
        i = 3
        While Not rs.EOF
          With activeSheet
            .Cells(i, 1) = CStr(rs(0).Value)
            .Cells(i, 2) = CStr(rs(2).Value)
            .Cells(i, 3) = CStr(rs(3).Value)
            .Cells(i, 4) = CStr(rs(4).Value)
          End With
          i = i + 1
          rs.MoveNext
        Wend
      End If

This code is very similar to the first verb. However, rather than a predictable list of information, the code loops through an unknown number of rows and populates the spreadsheet accordingly. The final results are shown in Figure 6.

Figure 6. Results after invoking verb 102

When the verb identifier is 201, then the code goes to the database to retrieve detailed order information for a specified order in the following manner:

      Set activeSheet = Target.Application.Sheets(3)
      activeSheet.Cells.Clear
      With cmd
        .CommandText = "sp_GetOrderDetail"
        Set p = .CreateParameter("@OrderID")
        p.Direction = adParamInput
        p.Type = adVarChar
        p.Size = 15
        p.Value = Properties.ValueFromIndex(0)
        .Parameters.Append p
        Set rs = .Execute()
      End With
      If Not rs.EOF Then
        activeSheet.Range("A1").Value = "Detail for order " _
          & Properties.ValueFromIndex(0)
        With activeSheet
          .Range("A2").Value = rs(1).Name
          .Range("B2").Value = rs(2).Name
          .Range("C2").Value = rs(3).Name
          .Range("D2").Value = rs(4).Name
        End With
        i = 3
        While Not rs.EOF
          With activeSheet
            .Cells(i, 1) = CStr(rs(1).Value)
            .Cells(i, 2) = CStr(rs(2).Value)
            .Cells(i, 3) = CStr(rs(3).Value)
            .Cells(i, 4) = CStr(rs(4).Value)
            rs.MoveNext
          End With
          i = i + 1
        Wend
      End If

This code is also quite similar to the previous verb. But, it activates a different sheet in the workbook before doing a very similar loop through the recordset. It also receives an identifier for an order rather than a customer. The final results are shown in Figure 7.

Figure 7. Results after invoking verb 201

Deploying the Smart Tag Solution

All that remains is to build and deploy the smart tag DLL. To build the solution, go to the File menu and click Build. In the Make Project dialog box, select your target directory, and click OK to build the project.

Once the DLL is built, you need to add a registry entry so that the host application knows that the DLL you just created is indeed one that is smart tag compliant and not just a generic DLL. You must make two entries, one for the CLSID of the recognizer and one for the CLSID of the action provider. The following are the full paths to these registry entries:

HKEY_CURRENT_USER\Software\Microsoft\Office\Common\Smart
Tag\Actions\{362D4069-7381-48E8-AD15-6580DEA0FD11}
HKEY_CURRENT_USER\Software\Microsoft\Office\Common\Smart
Tag\Recognizers\{7292CF2F-9BFE-4642-A415-35250ADCC2C4}

Before you can use the smart tag, you must restart Excel because it accesses the registry to find compliant smart tags only when starting. After starting Excel, you can type an ID in a cell and see the smart tag work.

Conclusion

Many companies have hundreds, and in many cases, thousands of spreadsheets loaded with data. However, far too often, the utility of those spreadsheets can diminish if they become too isolated from the rest of the company's information. Smart tags offer a way to connect spreadsheet data to a variety of sources, rendering them more contemporary and more fully integrated into daily tasks. Also, users can drill down into their data without having to learn a completely new user interface that takes them away from the focus of their work. The unobtrusive smart tag markup in the spreadsheet is the cue for users to dig deeper if they wish. Accessing the smart tag menu lets them find more information about a topic, an order, a customer, an employee, a product line, or virtually any other item of data that appears in a spreadsheet. Creating and deploying smart tags is one of the most exciting new avenues of development for Microsoft Office System. Smart tags enable users to explore detailed information in Excel. Connecting cell contents to enterprise data is merely a hint of the power this technology provides.

Use Smart Tags to Enable Data Drill-Down in Excel

Contents

Business Scenario

Creating the Project

Smart Tag Recognition

Smart Tag Actions

Deploying the Smart Tag Solution

Conclusion

Additional resources