HOW TO: Develop Database-Driven Smart Tags for Office by Using Visual Basic for Windows

Smart tag providers, for use in Office applications, are developed as ActiveX dynamic-link libraries (DLLs). Smart tag providers expose a visual, on-screen graphical user interface (GUI) when a user types a word or phrase that the provider recognizes. The GUI is similar to a hyperlink, but it has built-in intelligence that governs when it is available to the user. In short, it appears when it recognizes an appropriate word or phrase. In addition, its intelligence is extended by the fact that, based on what it has recognized, it can present a list of appropriate actions that the user might like to take. When a user chooses an action, the smart tag provider carries it out seamlessly.

This article describes the process of developing a database-driven smart tag provider that can provide useful information to employees as they undertake their work using applications such as Word and Excel. The steps that are necessary to create a simple smart tag provider that creates smart tags for employee names that are listed in the Northwind database are described. When the smart tags are implemented, users of Office can use the employee name smart tags to open a Web page simulating the employee details for the selected employee.

Requirements

The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs that you will need:

• Experience with Visual Basic development.
• Experience with implementing interfaces using Visual Basic.
• Experience using Office applications.

Create a Smart Tag Provider Project

To build a useful, database-driven smart tag provider, your DLL must implement two interfaces: the ISmartTagRecognizer interface and the ISmartTagAction interface.

To create a Visual Basic project for a smart tag provider, follow these steps:

1. Create a new ActiveX DLL project with Visual Basic 6.0. Rename the project as NWindEmployees.
2. On the Project menu, click References. Add a reference to the following libraries:
   • Microsoft Smart Tags 1.1 Type Library or later
   • Microsoft ActiveX Data Objects 2.6 Type Library or later
3. Rename the default class (Class1) in your ActiveX DLL project as SmartTagRecognizer.
4. Paste the following code at the top of the class module:

   Option Explicit
   
   Implements ISmartTagRecognizer
   					

5. Add a new class module to the project, and name the class SmartTagAction.
6. Paste the following code at the top of the SmartTagAction class module:

   Option Explicit
   
   Implements ISmartTagAction

Implement the SmartTagRecognizer Class

The SmartTagRecognizer class is used by smart tag-aware applications such as Word to determine the words that are recognized as smart tags. To implement this class, follow these steps:

1. Paste the following code in the SmartTagRecognizer class module, below the Implements statement:

   Private conNWind As ADODB.Connection
   Private rstEmployees As ADODB.Recordset
   Dim arrPeople() As String
   Dim iPeopleCount As Integer
   
   Private Sub Class_Initialize()
       'This procedure is fired when the recognizer class is initialized.
       'In this example, it retrieves a list of employee names that are
       'recognized as smart tags.
       Dim sConnection As String
       Dim sPeopleQuery As String
       Dim iCount As Integer
       sPeopleQuery = "SELECT FirstName FROM Employees"
       'The following connection string assumes a local SQL Server that supports
       'Integrated Security. You may need to change this connection string to
       'suit your specific SQL Server.
       sConnection = "Provider=SQLOLEDB.1;" _
         & "Initial Catalog=Northwind;" _
         & "Data Source=(local);" _
         & "Integrated Security=SSPI;"
       Set conNWind = New ADODB.Connection
       With conNWind
           .ConnectionString = sConnection
           .Open
       End With
       Set rstEmployees = New ADODB.Recordset
       rstEmployees.Open sPeopleQuery, conNWind
       ReDim arrPeople(0)
       iCount = 0
       Do While Not rstEmployees.EOF
           ReDim Preserve arrPeople(iCount)
           arrPeople(iCount) = UCase(rstEmployees("FirstName"))
           iCount = iCount + 1
           rstEmployees.MoveNext
       Loop
       iPeopleCount = UBound(arrPeople) + 1
       rstEmployees.Close
       conNWind.Close
       Set rstEmployees = Nothing
       Set conNWind = Nothing
   End Sub
   
   Private Property Get ISmartTagRecognizer_Desc(ByVal LocaleID As Long) As String
   'Description for this Smart Tag Recognizer.
       ISmartTagRecognizer_Desc = "Northwind Employees"
   End Property
   
   Private Property Get ISmartTagRecognizer_Name(ByVal LocaleID As Long) As String
   'Friendly name for this Smart Tag Recognizer.
       ISmartTagRecognizer_Name = "Northwind Employee Recognizer"
   End Property
   
   Private Property Get ISmartTagRecognizer_ProgId() As String
   'ProgID used to inform Office applications that this Recognizer exists.
       ISmartTagRecognizer_ProgId = "NWindEmployees.SmartTagRecognizer"
   End Property
   
   Private Sub ISmartTagRecognizer_Recognize(ByVal Text As String, ByVal DataType As SmartTagLib.IF_TYPE, ByVal LocaleID As Long, ByVal RecognizerSite As SmartTagLib.ISmartTagRecognizerSite)
   'This method is called by smart tag-aware applications such as Word.
   'It is used to create the list of smart tags that are recognized.
       Dim iTerms As Integer
       Dim iPos As Integer, iLen As Integer
       Dim pBag As SmartTagLib.ISmartTagProperties
       Text = UCase(Text)
       For iTerms = 0 To iPeopleCount - 1
           iPos = InStr(Text, arrPeople(iTerms))
           iLen = Len(arrPeople(iTerms))
           Do While iPos > 0
               Set pBag = RecognizerSite.GetNewPropertyBag
               RecognizerSite.CommitSmartTag _
                   "schemas-nwind-com/employee#names", iPos, iLen, pBag
               iPos = InStr(iPos + iLen, Text, arrPeople(iTerms))
           Loop
       Next iTerms
   End Sub
   
   Private Property Get ISmartTagRecognizer_SmartTagCount() As Long
   'Number of smart tag types implemented in this recognizer.
       ISmartTagRecognizer_SmartTagCount = 1
   End Property
   
   Private Property Get ISmartTagRecognizer_SmartTagDownloadURL(ByVal SmartTagID As Long) As String
   'URL associated with the smart tags implemented by this recognizer.
       ISmartTagRecognizer_SmartTagDownloadURL = ""
   End Property
   
   Private Property Get ISmartTagRecognizer_SmartTagName(ByVal SmartTagID As Long) As String
   'This property defines the name for each smart tag type implemented.
       If (SmartTagID = 1) Then
           ISmartTagRecognizer_SmartTagName = "schemas-nwind-com/employee#names"
       End If
   End Property
   					

2. Ensure that the sConnection assignment in the Class_Initialize procedure refers to your actual SQL Server computer.

Implement the SmartTagAction Class

In this procedure, you implement ten property procedures and an InvokeVerb method for the SmartTagAction class. This class is used by smart tag-aware applications to associate actions with a smart tag, and to generate the GUI displayed for the smart tag. To implement the SmartTagAction class, follow these steps:

1. View the code window for the SmartTagAction class module.
2. Paste the following code in the class module below the Implements statement:

   Private Property Get ISmartTagAction_Desc(ByVal LocaleID As Long) As String
   'Description for the action class.
       ISmartTagAction_Desc = "Provides actions for Northwind Employee data"
   End Property
   
   Private 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)
   'This method is called by smart tag aware applications (such as Word).
   'It performs the requested smart tag action.
       Dim ie
       If (VerbID = 1) Then
           Set ie = CreateObject("InternetExplorer.Application")
           ie.Navigate2 ("www.northwindtraders.com/employees/" & Text & ".html")
           ie.Visible = True
       End If
   End Sub
   
   Private Property Get ISmartTagAction_Name(ByVal LocaleID As Long) As String
   'Friendly name for the action class.
       ISmartTagAction_Name = "Northwind Employees Actions"
   End Property
   
   Private Property Get ISmartTagAction_ProgId() As String
   'ProgID for the action class.
       ISmartTagAction_ProgId = "NWindEmployees.SmartTagAction"
   End Property
   
   Private Property Get ISmartTagAction_SmartTagCaption(ByVal SmartTagID As Long, ByVal LocaleID As Long) As String
   'Returns the caption for the smart tags.
       ISmartTagAction_SmartTagCaption = "Northwind Employees"
   End Property
   
   Private Property Get ISmartTagAction_SmartTagCount() As Long
   'Returns the number of smart tags this class provides actions for.
       ISmartTagAction_SmartTagCount = 1
   End Property
   
   Private Property Get ISmartTagAction_SmartTagName(ByVal SmartTagID As Long) As String
   'Returns the name for the smart tags.
       If (SmartTagID = 1) Then
           ISmartTagAction_SmartTagName = "schemas-nwind-com/employee#names"
       End If
   End Property
   
   Private Property Get ISmartTagAction_VerbCaptionFromID(ByVal VerbID As Long, ByVal ApplicationName As String, ByVal LocaleID As Long) As String
   'This property returns the action caption for a specified ID.
       If (VerbID = 1) Then
           ISmartTagAction_VerbCaptionFromID = "View Employee Information"
       End If
   End Property
   
   Private Property Get ISmartTagAction_VerbCount(ByVal SmartTagName As String) As Long
   'Returns the number of available actions.
       If (SmartTagName = "schemas-nwind-com/employee#names") Then
           ISmartTagAction_VerbCount = 1
       End If
   End Property
   
   Private Property Get ISmartTagAction_VerbID(ByVal SmartTagName As String, ByVal VerbIndex As Long) As Long
   'Provides a unique ID for each action.
       ISmartTagAction_VerbID = VerbIndex
   End Property
   
   Private Property Get ISmartTagAction_VerbNameFromID(ByVal VerbID As Long) As String
   'This property returns the action name for a specified ID.
       If (VerbID = 1) Then
           ISmartTagAction_VerbNameFromID = "viewEmployeeInfo"
       End If
   End Property
   					

Compile and Register your Smart Tag Provider

After your smart tag provider is complete, you should compile it as an ActiveX DLL in the normal manner, and then register the DLL using regsvr32 command. After you compile and register the DLL, you need to edit the registry so that Office applications can find your smart tag provider. To do this, follow these steps:

1. In your Visual Basic project, compile the Smart Tag Provider DLL by clicking Make NWindEmployees.dll on the File menu.
2. On the Start menu, click Run.
3. Type regedit.exe and click OK to start the registry editor.
4. Expand the following registry key:
   ���[HKEY_CURRENT_USER\Software\Microsoft\Office\Common\Smart Tag
5. Right-click Actions, click New, and then click key to create a new key named NWindEmployees.SmartTagAction.
6. Right-click Recognizers, click New, and then click key to create a new key named NWindEmployees.SmartTagRecognizer.
7. Close the registry editor.

Test your Smart Tag Provider

To test your smart tag provider, follow these steps:

1. Start Word. If Word is already open, close all instances of Word and restart it.
2. On the Tools menu, point to Macro and then click Security. Set the macro security to Medium and click OK. If the macro security setting was previously set to High, restart Word.
3. Type Andrew is our Vice President.
4. Note that the word "Andrew" is underlined with purple dots.
5. Move the mouse over the word Andrew. An information symbol appears.
6. Click the information symbol.
7. Click View Employee Information. Microsoft Internet Explorer appears.
8. Internet Explorer initially attempts to browse to http://www.northwindtraders.com/employees/Andrew.html. However, all requests to www.northwindtraders.com result in you being redirected to Microsoft's home page.
9. Close Internet Explorer.
10. Close Word without saving changes to the document.

Troubleshooting

Ensure that no Office applications are running when you register your DLL.

For more troubleshooting information for developing smart tags in Visual Basic 6.0, refer to the "Troubleshooting" section in the following Microsoft Knowledge Base article: For more information on troubleshooting, including information on macro security, see the following Knowledge Base article: The Office XP Smart Tag Software Development Kit (SDK) provides further information about building smart tag providers. You can download the SDK from the following Microsoft Developer Network (MDSN) Web site: