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
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!