Creating an Office COM add-in: getting started for VB developers

In today’s article I’ll take a break from my usual C# and return to my roots as a VB developer. We’ll take a whirlwind trip through some of the features of Add-in Express and how to use it using Visual Studio 2010 and Visual Basic.Net

Getting Started

Let’s start by creating a new ADX COM add-in, which is available under Installed Templates> Other Project Types> Extensibility.

Click OK and you’ll be presented with the New Microsoft Office COM Add-in wizard. On the first screen you can select your programming language, which in our case will be Visual Basic. You should also choose which version of Office will be the minimum version your add-in supports. Bear in mind that certain features are only available in newer versions of Office, so choose carefully (for more info, please see Supporting several Office versions in an add-in. Interop assemblies and late binding.). In our scenario we’ll choose Microsoft Office 2007.

Click Next. Add-in Express allows you to target multiple Microsoft Office applications with one add-in. We’ll select Microsoft Excel, Word and Outlook as the supported applications.

Click Next. You can choose to generate a new strong name file or use an existing one. Choose Generate new for now. If you need to use another string name file later on, you can select it in your projects’ properties Signing tab.

Click Finish to complete the wizard, the Add-in Module Designer should then open automatically, if not, open it by double-clicking on AddinModule.vb in the Solution Explorer.

Need Help and the latest version?

Add-in Express added a Help panel at the bottom of the AddinModule designer surface to help you quickly access the wealth of help and resources on their website. You can also use it to quickly check which version of Add-in Express you’re using.

Adding UI elements

Add a ribbon tab by clicking on the ADXRibbonTab button on the designer toolbar. Select the ribbon tab component and expand the designer by clicking on the two up arrows. Using the designer toolbar, add three ribbon groups (ADXRibbonGroup) and also add a button to each group.

Set the Ribbon Tab’s Ribbons property to OutlookMailRead;OutlookContact;ExcelWorkbook;WordDocument and choose where the ribbon groups should appear by setting the Ribbons property for each:

• OutlookRibbonGroup.Ribbons = OutlookMailRead;OutlookContact
• ExcelRibbonGroup.Ribbons = ExcelWorkbook
• WordRibbonGroup = WordDocument

By setting the Ribbons’ property you can choose where your UI elements should show. In the above example the Outlook group will only show in Outlook when an e-mail is read and when the user opens a Contact item.

Using the Office objects

Now that we have all the necessary UI elements, let’s have a look at the code behind the scenes. Switch to the Add-in Module’s code by clicking on the View Code button on the Solution Explorer toolbar. You’ll see the three important properties we need in order to gain access to the MS Office objects:

Public ReadOnly Property ExcelApp() As Excel._Application
        Get
            Return CType(HostApplication, Excel._Application)
        End Get
    End Property
 
    Public ReadOnly Property WordApp() As Word._Application
        Get
            Return CType(HostApplication, Word._Application)
        End Get
    End Property
 
    Public ReadOnly Property OutlookApp() As Outlook._Application
        Get
            Return CType(HostApplication, Outlook._Application)
        End Get
    End Property

Each of these properties exposes the running MS Office application and allows you to gain access to it. Add an event handler for the OutlookRibbonButton‘s OnClick event by selecting it in the code-behind object dropdown, and selecting the OnClick event in the event dropdown.

This will automatically generate the event handler code for you. Add the following code to it:

Private Sub OutlookRibbonButton_OnClick _
		(sender As Object, control As AddinExpress.MSO.IRibbonControl, pressed As Boolean) _
		Handles OutlookRibbonButton.OnClick
 
        Dim currentInspector As Outlook.Inspector = TryCast(OutlookApp.ActiveInspector, _
			Outlook.Inspector)
 
        If currentInspector IsNot Nothing Then
            Dim item as Object = currentInspector.CurrentItem
            If TypeOf (item) Is Outlook.MailItem Then
                Dim currentMailItem As Outlook.MailItem = TryCast(item, Outlook.MailItem)
                MessageBox.Show("The e-mail subject is : " & currentMailItem.Subject)
            ElseIf TypeOf (item) Is Outlook.ContactItem Then
                Dim currentContactItem As Outlook.ContactItem = TryCast(item, _
					Outlook.ContactItem)
                MessageBox.Show("The contact is : " & currentContactItem.FullName)
            End If
            Marshal.ReleaseComObject(item)
            Marshal.ReleaseComObject(currentInspector)
        End If
    End Sub

The code will check the type of the current Inspector item, and show a messagebox specific to the item type. Next, add an event handler for the Excel button in a similar fashion as above and add the following code:

Private Sub ExcelRibbonButton_OnClick _
        (sender As Object, control As AddinExpress.MSO.IRibbonControl, pressed As Boolean) _
        Handles ExcelRibbonButton.OnClick
 
        Dim currentSheet As Excel.Worksheet = TryCast(ExcelApp.ActiveSheet, _
	Excel.Worksheet)
        currentSheet.Cells(1, 1).Value = "Process Id"
        currentSheet.Cells(1, 2).Value = "Process Name"
        currentSheet.Range("A1", "B1").Font.Bold = True
 
        Dim rowCount As Integer = 2
        Dim processList() As Diagnostics.Process
        processList = Diagnostics.Process.GetProcesses()
        For Each process As Diagnostics.Process In processList
            currentSheet.Cells(rowCount, 1).Value = process.Id
            currentSheet.Cells(rowCount, 2).Value = process.ProcessName
            rowCount += 1
        Next
        Marshal.ReleaseComObject(currentSheet)
    End Sub

This code will loop through a list of running processes on your system and populate the active worksheet in the Excel workbook. Finally add an event handler for the Word ribbon button, add the following code to it:

Private Sub WordRibbonButton_OnClick _
        (sender As Object, control As AddinExpress.MSO.IRibbonControl, pressed As Boolean) _
        Handles WordRibbonButton.OnClick
        Dim currentDoc As Word.Document = TryCast(WordApp.ActiveDocument, Word.Document)
        Dim myTable As Word.Table = currentDoc.Tables.Add(currentDoc.Range, 1, 2, _
			Nothing, Nothing)
        myTable.Cell(1, 1).Range.Text = "Process Id"
        myTable.Cell(1, 1).Range.Font.Bold = True
        myTable.Cell(1, 2).Range.Text = "Process Name"
        myTable.Cell(1, 2).Range.Font.Bold = True
 
        Dim rowCount As Integer = 2
        Dim processList() As Diagnostics.Process
        processList = Diagnostics.Process.GetProcesses()
        For Each process As Diagnostics.Process In processList
            myTable.Rows.Add()
            myTable.Cell(rowCount, 1).Range.Text = process.Id
            myTable.Cell(rowCount, 1).Range.Font.Bold = False
            myTable.Cell(rowCount, 2).Range.Text = process.ProcessName
            myTable.Cell(rowCount, 2).Range.Font.Bold = False
            rowCount += 1
        Next
 
        Marshal.ReleaseComObject(myTable)
        Marshal.ReleaseComObject(currentDoc)
    End Sub

The above code will perform a similar task as the Excel button, but this time it will add the list of running processes to a MS Word table.

Testing your add-in

Once you’re satisfied with your UI and code logic, build your project by selecting Build Solution from the Visual Studio Build menu. After the project has been built successfully, select Register ADX Project from the same Build menu, this will register your Add-in for use by MS Office. After the project has been registered, run your project by pressing F5. If you want to change the MS Office application you wish to test with, go to your projects’ property pages and on the Debug tab set the Start external program value to the desired MS Office application.

Thank you for reading. Until next time, keep coding!

Available downloads:

This sample add-in was developed using Add-in Express 2010 for Microsoft Office and .net
VB.NET sample Office add-in