Migrating a VBA Solution to a Visual Studio 2005 Tools for the Office System SE Add-in

Summary: Learn how to use Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System to add new functionality to your Visual Basic for Applications (VBA) solutions, using the VBA skills you already have. (31 printed pages)

Jan Fransen, MCW Technologies, LLC

October 2006

Applies to: Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Office Word 2007

Contents

• Types of Microsoft Office Solutions

• Office Solution Architectures

• A Global Template Example: The Amazon Add-In

• Migrating the VBA Global Template to an Add-In

• Conclusion

• Additional Resources

Developers and power users of Microsoft Office have been writing code to customize and extend Office almost since the first Office products were introduced. Most Office solutions in use today were built by using Microsoft Visual Basic for Applications (VBA). For document-based products such as Microsoft Office Word and Microsoft Office Excel, solutions generally fall into one of three categories:

• Document-based solutions

• Template-based solutions

• Templates deployed as application-level solutions, usually referred to as global templates or add-ins

If you prefer not to use VBA, you can create any of these three types of solutions by using Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio Tools for Office).

Up until now, though, it has been problematic to create robust application-level add-ins, as opposed to the document-level customizations listed above, using Microsoft Visual Studio. Those difficulties are resolved with the newest version of Visual Studio Tools for Office, named Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System (also known as Visual Studio 2005 Tools for Office Second Edition), which is available as a free download.

Overall, VBA has been a hugely successful tool for creating custom Office solutions. Before considering the issues involved in migrating solutions from VBA to Visual Studio 2005 Tools for Office Second Edition, you may find it helpful to think about how Office solutions got to this point.

Most Office solutions address specific issues that are relevant only for a single document or for documents of a certain type.

If a customization is relevant only to a single document, you create a document-based solution. The code resides either in a VBA project within that document, or in a separate Visual Studio Tools for Office assembly. If you intend to create multiple documents of a single type, your best option is to create a document template. A template is just a document with a different extension; Word templates use the .dot extension instead of .doc, and Excel templates use .xlt instead of .xls.

When you create a new document based on your template, the new document inherits the interactive content of the template—such as worksheets and the data they contain, paragraphs of text, and styles. The code itself may or may not be part of the new document, depending on the product and on whether you created the document by using VBA or Visual Studio Tools for Office. With Word and Excel, there are three possible scenarios:

• If you create an Excel document based on a template that contains VBA code, the new workbook also contains a copy of the template's VBA project.

• If you create a Word document based on a template that contains VBA code, the new document has the template's interactive content, but not the code. Instead, Word loads the template in the background and runs the code from the template's VBA project.

• If you create either an Excel document or a Word document based on a template you created by using Visual Studio Tools for Office, the new document (and the template itself) references the Visual Studio Tools for Office assembly, but does not itself contain any code.

Some solutions customize more than just one document or a set of documents. These solutions add new features to an Office product and have those features available no matter what document is open.

If your solution uses Word or Excel, you can change a template-based solution to an application-based solution by copying the template to the Office product's Startup folder (Word) or XLStart folder (Excel). You will often hear this type of Office solution referred to as a global template.

When Word or Excel starts, any templates in the Startup (or XLStart) folder are loaded. The document does not appear on the screen, but its code is loaded and can be called from a control such as a command-bar button, by an event, or from code that you write yourself.

In Microsoft Office 2000, Microsoft introduced the Component Object Model (COM) add-in. A COM add-in is an application written outside Office—in Microsoft Visual Basic, for example—and compiled as a DLL. The DLL can be loaded by one or more Office applications and can interact with the Office object models. COM add-ins enable you to use your programming language and environment of choice when creating Office-based solutions.

The first COM add-ins were most often written using Microsoft Visual Basic 6.0 or Microsoft Visual C++. These days, you can use the shared add-in project template provided in Visual Studio 2005 to build COM add-ins with Visual Basic, Microsoft Visual C#, or C++. Although it is nice to be able to write add-ins in the Visual Studio environment, add-ins created by using the shared add-in template are not without their problems. You can read full details about shared add-in limitations in the technical article Migrating a Shared Add-in to a Visual Studio 2005 Tools for the Office System SE Add-in.

With the release of Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft began to address the shared add-in disadvantages by adding a template for creating add-ins for Microsoft Office Outlook. Add-ins created by using this template are often referred to as VSTO add-ins to differentiate them from COM add-ins.

Visual Studio 2005 Tools for Office Second Edition takes VSTO add-ins a step further by extending them to additional Office products, including the following:

• Microsoft Office Excel 2003 and Microsoft Office Excel 2007

• Microsoft Office InfoPath 2007

• Microsoft Office Outlook 2003 and Microsoft Office Outlook 2007

• Microsoft Office PowerPoint 2003 and Microsoft Office PowerPoint 2007

• Microsoft Office Visio 2003 and Microsoft Office Visio 2007

• Microsoft Office Word 2003 and Microsoft Office Word 2007

Visual Studio Tools for Office developers generally fall into two groups: people who already use Visual Studio to develop Microsoft Windows applications and who now want to create solutions that are based on Microsoft Office, and people who create solutions by using built-in Office tools but who have run into limits with the VBA language or environment. This article addresses the latter group.

As an Office developer, you have probably created a number of VBA-based solutions. As you explore Visual Studio 2005 Tools for Office Second Edition, you may find that it does not make sense to migrate all of your solutions. If a solution has been running well for years and you do not plan to add anything new to it, it is probably best to leave it where it is. If you design something new, or plan major enhancements to an existing solution, or if you would like to change a document-level solution into an application-level solution, you should look carefully at what Visual Studio 2005 Tools for Office Second Edition offers. Visual Studio 2005 Tools for Office Second Edition provides the following improvements:

• Richer development environment

• Easier code reuse

• Better security model

• More flexible deployment and versioning

• Easier integration with other resources such as Web services or data stored in a database or in XML

• Extended user interface options, through the use of Windows Forms

• For Word and Excel document-based and template-based solutions, the ability to programmatically control the Document Actions task pane

• For applications in the 2007 Microsoft Office system, the ability to create and use custom task panes and programmatically customize the Ribbon

At the end of this article, you can find links to articles that describe these advantages in detail.

You will make your development decisions based on what works best for you and your organization. This article uses an example—an add-in written by using VBA and deployed as a global template—to show you some of the advantages of the Visual Studio 2005 Tools for Office Second Edition environment and some of the issues you should consider.

The Amazon add-in example solution enables the user to query Amazon's book database and add information about a specific book to the active document. To get started, I created a template for Word and another for Excel. The VBA code contained in the templates is nearly identical; the only difference is in the object model code that interacts with the active document or workbook. The add-in runs from a command button on a custom command bar that is stored with the template and displayed when the application opens, as shown in Figure 1.

Figure 1. The Amazon command bar is available to all documents

When the user clicks Search Amazon, the form shown in Figure 2 opens.

Figure 2. The form prompts the user for a keyword and returns book information

The user types a keyword and clicks Search. The code calls the Amazon E-Commerce Service (ECS) and queries for books with keywords that match the exact word or phrase entered by the user. If books are found, the Amazon ECS returns information about the books in XML format. The code parses the XML and adds the first ten books found to the form's list box.

After the list is created, the user can select a book and click Add to add information about the book to the active document. If you are working in Word, the code adds citation information, as shown in Figure 3.

Figure 3. Citation information is added at the cursor location

If you are working in Excel, the code adds information about the book to a range in the active worksheet, as shown in Figure 4.

Figure 4. Book information is added at or under the active cell

The form and most of the code in the global templates might be familiar to VBA developers. The code does two things, though, that may be less familiar: interacting with a Web service and navigating an XML document. Those areas require a bit more explanation.

A Web service is an application hosted on an Internet server that responds to requests with XML-formatted data. You can create Web services yourself, but this example uses one of several Web services created and maintained by Amazon.com that are available to the public.

Although there is no charge to use the Amazon.com Web services, you do need to create an Amazon Web Services account. You can create an account at the Amazon.com Amazon Web Services Store. After your account is active, Amazon issues you an Access Key ID. You need that Access Key ID to request information from any of the Amazon Web services (and to run the sample add-ins provided with this article).

Amazon provides several different Web services. The one I am using is the Amazon E-Commerce Service (ECS). ECS provides access to much of the data and functionality you see when you use Amazon.com as a customer: product information (including images and customer reviews), shopping carts, wish lists, and advanced search capability. For these examples, I use a tiny fraction of what Amazon ECS provides. The add-ins perform simple keyword searches for books, and provide product information for only the first ten items returned by any search. If you want to use the Amazon ECS in your own applications, see the Additional Resources list at the end of this article.

The examples in this article use SOAP to communicate with the Amazon ECS. A Web service that supports SOAP publishes a Web Services Description Language (WSDL) document that describes both the format you use to make requests and the format you can expect for the Web service's response. In VBA, you can use the Microsoft Office 2003 Web Services Toolkit, which is available as a free download, to read the WSDL document and build VBA classes that interact with the Web service. In Visual Studio, you can add a reference to a Web service. Visual Studio creates all the code necessary and you interact with the Web service as a series of objects.

Web services require that you send your request as an XML string. You can find the code to build the request packet in the frmAmazon form's cmdSearch_Click procedure:

Dim strRequest As String

strRequest = _
   "<ItemSearch>" & _
   "<AWSAccessKeyId>{0}</AWSAccessKeyId>" & _
   "<Request>" & _
   "<Keywords>""{1}""</Keywords>" & _
   "<SearchIndex>Books</SearchIndex>" & _
   "<ResponseGroup>Medium</ResponseGroup>" & _
   "</Request>" & _
   "</ItemSearch>"

strRequest = Replace(strRequest, "{0}", conDevKey)
strRequest = Replace(strRequest, "{1}", Me.txtKeyword)

The conDevKey constant is defined in the Declarations section of the form's code module. To run the example, replace the string with your Access Key ID.

Private Const conDevKey = "Your Access Key ID goes here"

The replacement value Me.txtKeyword refers to the Keyword text box on the form.

All Web services, including the Amazon ECS, communicate by using XML. For example, Figure 5 shows what an Amazon ECS response might look like.

Figure 5. A portion of the response from Amazon ECS

To send your request to the service and receive and work with the response programmatically, you can use the Microsoft XML (MSXML) object model. In a VBA project, you set a reference to Microsoft XML, v5.0, as shown in Figure 6.

Figure 6. Use MSXML to work with XML in VBA code

The XML returned by the ECS can be loaded into a DOMDocument object for parsing. The following code is what I used to send the request and receive the response.

Dim AmazonWS As New clsws_AWSECommerceService
Dim strRequest As String
Dim xmlDocRequest As New DOMDocument
Dim xmlResponseNodes As IXMLDOMNodeList

strRequest = "<ItemSearch>" & _
   "<AWSAccessKeyId>{0}</AWSAccessKeyId>" & _
   "<Request>" & _
   "<Keywords>""{1}""</Keywords>" & _
   "<SearchIndex>Books</SearchIndex>" & _
   "<ResponseGroup>Medium</ResponseGroup>" & _
   "<Sort>salesrank</Sort>" & _
   "</Request>" & _
   "</ItemSearch>"
strRequest = Replace(strRequest, "{0}", conDevKey)
strRequest = Replace(strRequest, "{1}", Me.txtKeyword)

xmlDocRequest.loadXML (strRequest)
Set xmlResponseNodes = _
    AmazonWS.wsm_ItemSearch(xmlDocRequest.SelectNodes("//"))

The wsm_ItemSearch method returns an XML node list. I loaded the response into an XML DOMDocument. Then I used IXMLDOMElement objects and XPath expressions to select the data I wanted in the form. In the example, I loaded each book's ASIN (Amazon's unique identifier), title, and author or authors into an array and then used the array to populate a list box on the form.

Dim AmazonWS As New clsws_AWSECommerceService
Dim strRequest As String
Dim xmlDocRequest As New DOMDocument
Dim xmlResponseNodes As IXMLDOMNodeList

strRequest = "<ItemSearch>" & _
   "<AWSAccessKeyId>{0}</AWSAccessKeyId>" & _
   "<Request>" & _
   "<Keywords>""{1}""</Keywords>" & _
   "<SearchIndex>Books</SearchIndex>" & _
   "<ResponseGroup>Medium</ResponseGroup>" & _
   "<Sort>salesrank</Sort>" & _
   "</Request>" & _
   "</ItemSearch>"
strRequest = Replace(strRequest, "{0}", conDevKey)
strRequest = Replace(strRequest, "{1}", Me.txtKeyword)

xmlDocRequest.loadXML (strRequest)
Set xmlResponseNodes = _
    AmazonWS.wsm_ItemSearch(xmlDocRequest.SelectNodes("//"))

Dim xmlRoot As IXMLDOMElement
Dim xmlBook As IXMLDOMElement
Dim xmlAuthor As IXMLDOMElement
Dim strAuthor As String
Dim intCount As Integer

Set xmlDocResponse = New DOMDocument
xmlDocResponse.loadXML (xmlResponseNodes(1).xml)
Set xmlRoot = xmlDocResponse.documentElement

intBooksFound = xmlRoot.SelectSingleNode("/Items/TotalResults").Text
Select Case intBooksFound
    Case 0
        MsgBox (xmlRoot.SelectSingleNode( _
            "/Items/Request/Errors/Error/Message").Text)
        Exit Sub
    Case Is > 10
        intBooksFound = 10
End Select
ReDim arBooks(intBooksFound, 3)

For Each xmlBook In xmlRoot.ChildNodes
    If xmlBook.tagName = "Item" Then
        arBooks(intCount, 0) = xmlBook.SelectSingleNode("ASIN").Text
        arBooks(intCount, 1) = _
            xmlBook.SelectSingleNode("ItemAttributes/Title").Text
        strAuthor = ""
        intAuthorTotal = _
            xmlBook.SelectNodes("ItemAttributes/Author").Length
        For intAuthorCount = 0 To intAuthorTotal - 1
            Call BuildAuthor(strAuthor, _
                xmlBook.SelectNodes( _
                "ItemAttributes/Author").Item(intAuthorCount).Text, _
                intAuthorCount, intAuthorTotal)
        Next intAuthorCount
        arBooks(intCount, 2) = strAuthor
        intCount = intCount + 1
    End If
Next xmlBook
Me.lstBooks.List = arBooks

After the list is filled, the user can interact with the form and select items from the list to add to the document or worksheet. For the VBA version of the add-in, I chose to keep the DOMDocument open and use it to get any additional information I wanted.

In the VBA add-in, the list box contains only the ASIN, book title, and book authors. The ASIN is not displayed to the user, but the BoundColumn property of the list box is set to the ASIN column. When the user clicks Add, code runs to select the Item node with the selected ASIN value and collect other information about the book from that node. The following code collects that information for the Word add-in.

Dim strTitle As String, strYear As String, strPublisher As String

Set xmlBook = xmlDocResponse.documentElement.SelectSingleNode( _
    "//Item[ASIN='" & strASIN & "']")

strTitle = xmlBook.SelectSingleNode("ItemAttributes/Title").Text
strYear = _
    Left(xmlBook.SelectSingleNode( _
    "ItemAttributes/PublicationDate").Text, 4)
strPublisher = _
    xmlBook.SelectSingleNode("ItemAttributes/Publisher").Text

The code used in the Excel add-in is similar, except that it also finds the FormattedPrice value.

strPrice = _
    xmlBook.SelectSingleNode( _
    "ItemAttributes/ListPrice/FormattedPrice").Text

After the data is collected, I use straightforward object model code to add it to the document. The following code adds a book citation to the active Word document at the position of the cursor.

Dim rng As Range
    Set rng = Selection.Range
    With rng
        .InsertAfter strAuthor & " (" & strYear & "). "
        .Collapse wdCollapseEnd
        .InsertAfter strTitle
        .Font.Italic = True
        .Collapse wdCollapseEnd
        .InsertAfter ". " & strPublisher
        If Right(strPublisher, 1) <> "." Then
            .InsertAfter "."
        End If
        .Font.Italic = False
        .InsertParagraphAfter
    End With

The following code adds book information to a blank row in the active Excel worksheet.

Dim intColumn As Integer, intRow As Integer
Selection.EntireRow.Insert
intRow = ActiveCell.Row
intColumn = ActiveCell.Column
Cells(intRow, intColumn) = strTitle
Cells(intRow, intColumn + 1) = strAuthor
Cells(intRow, intColumn + 2) = strPublisher
Cells(intRow, intColumn + 3) = strYear
Cells(intRow, intColumn + 4) = strPrice

In its VBA incarnation, the Amazon add-in runs acceptably. Imagine, though, that my company is moving to the 2007 release of Microsoft Office. Although the Amazon add-in will continue to work without modification in Word 2007 and Excel 2007, I now have an opportunity to consider what I could gain by moving my add-in to Visual Studio 2005 Tools for Office Second Edition.

I will set aside personal preferences about programming environment and language choice. I am not concerned about security with this particular add-in. I am, however, very interested in the changes I could make to the add-in's user interface by using Visual Studio 2005 Tools for Office Second Edition. I would like to use a custom task pane in place of the VBA user form. A task pane puts the add-in's user interface beside the document rather than covering part of it. Working with Visual Studio 2005 Tools for Office Second Edition also means I can use any Windows Forms controls I like, to create a more attractive and useful user interface. Because I will be working with applications in the 2007 release of Office, I could also write code to customize the Ribbon instead of using a command button. But this add-in requires only a single button, so I will keep the migration simple by continuing to use the command button. For information about how to work with the Ribbon programmatically, see the Additional Resources list.

The remainder of this article walks you through the steps you might take to build a new version of the Amazon add-in by using Visual Studio 2005 Tools for Office Second Edition. The article also demonstrates the following tasks:

• Creating a custom task pane

• Getting an image from a URL and displaying it by using an Image control

• Storing data of different types in a DataGridView control

• Using the System.XML class to work with XML documents

Visual Studio 2005 Tools for Office Second Edition provides an add-in project template for each application that supports Visual Studio 2005 Tools for Office Second Edition add-ins.

1. Start Visual Studio 2005.

2. On the File menu, point to New, and then click Project.

   The New Project dialog box appears.

3. Expand either the Visual Basic node or the C# node.

4. Expand the Office node and select 2007 Add-ins, as shown in Figure 7.

   Figure 7. Select an add-in

   

5. In the Templates pane, select either Excel Add-in or Word Add-in.

6. Type the name AmazonAddIn and a location for your add-in.

7. Click OK.

Visual Studio uses the template you selected to create a new solution with two projects. One project is the add-in project itself, and the other is a Setup project you can use to deploy your completed add-in. The add-in project contains a class named ThisAddIn with two event handlers: Startup and Shutdown.

The add-in will be loaded when the application starts, but the user needs to have a way of opening the custom task pane. In the global templates, the UserForm instance opened when the user clicked a command button. I created the command button myself when I designed the template. Visual Studio 2005 Tools for Office Second Edition add-ins, though, are not associated with a particular document, so there is no place to interactively create the button. Instead, you include code to create the button programmatically as the add-in starts.

To create a custom toolbar and command button for the add-in, start by adding variables for the new command bar and button to the Declarations section of the class. You use the button's Click event, so if you use Visual Basic, declare the variable using the WithEvents keyword. In C#, you need another line of code, shown later, to hook up the event procedure.

public class ThisAddIn
    Private AddInMenuBar As Office.CommandBar
    Private WithEvents OpenTaskPaneButton As Office.CommandBarButton

public partial class ThisAddIn
{
    private Office.CommandBar AddInMenuBar;
    private Office.CommandBarButton OpenTaskPaneButton;

You can use the Startup method to create a new command bar and button. Note that the Excel 2007 add-in project template includes a line of code to set up the Application object. I have not included that line of code here.

Private Sub ThisAddIn_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup
    Try
        AddInMenuBar = _
            Me.Application.CommandBars.Add("Amazon", Temporary:=True)
        OpenTaskPaneButton = DirectCast(AddInMenuBar.Controls.Add( _
            Office.MsoControlType.msoControlButton, Temporary:=True), _
            Office.CommandBarButton)
        OpenTaskPaneButton.Caption = "Search Amazon"
        OpenTaskPaneButton.Style = _
            Microsoft.Office.Core.MsoButtonStyle.msoButtonCaption
        AddInMenuBar.Visible = True

    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, ex.Source)
    End Try
End Sub

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    try
    {
        AddInMenuBar = Application.CommandBars.Add(
            "Amazon", missing, missing, true);
        OpenTaskPaneButton = (Office.CommandBarButton)
            (AddInMenuBar.Controls.Add( 
            Office.MsoControlType.msoControlButton,
            missing,missing, missing,true));
        OpenTaskPaneButton.Caption = "Search Amazon";
        OpenTaskPaneButton.Style = 
        Microsoft.Office.Core.MsoButtonStyle.msoButtonCaption;
            OpenTaskPaneButton.Click += new 
            Office._CommandBarButtonEvents_ClickEventHandler(
            OpenTaskPaneButton_Click);
        AddInMenuBar.Visible  = true;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, ex.Source, 
            MessageBoxButtons.OK,MessageBoxIcon.Error);
    }
}

A very simple event handler for the button might look like the following code.

Private Sub OpenTaskPaneButton_Click( _
    ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, _
    ByRef CancelDefault As Boolean) Handles OpenTaskPaneButton.Click

    MsgBox("The button has been clicked")
End Sub

private void OpenTaskPaneButton_Click(Office.CommandBarButton Ctrl, 
    ref bool CancelDefault)
{
    MessageBox.Show("The button has been clicked");
}

When the add-in exits, the Shutdown event handler runs. You should write code in the Shutdown event handler to remove the button and the command bar.

Private Sub ThisAddIn_Shutdown(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Shutdown

    OpenTaskPaneButton.Delete(False)
    OpenTaskPaneButton = Nothing
    AddInMenuBar = Nothing
End Sub

private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
    OpenTaskPaneButton.Delete(false);
    OpenTaskPaneButton = null;
    AddInMenuBar = null;
}

At this point, the add-in contains enough code to run and actually show something. On the Debug menu, click Start Debugging. The target application (Word or Excel) starts. Custom command bars in add-ins or documents are added to the application's Add-Ins tab, as shown in Figure 8.

Figure 8. Find your command bar and button by selecting the Add-Ins tab

If you have worked only with VBA user forms before, take some time to look through the Windows Forms controls as you design your new user interface. The set of controls is much larger and much richer than what is available to the VBA developer. See the Additional Resources list for more information about the different controls; this article describes only a few:

• The User Control, which acts as a container for all controls you want to place on a custom task pane

• The DataGridView control, for storing and displaying the list of items returned by the Amazon ECS

• The PictureBox control, for displaying a small image of the selected book

• The familiar TextBox control and Button control, for typing in the keyword, running the search, and adding content to the active document

To create a new user control, click the Project menu, and then click Add User Control. Use the Add New Control dialog box to specify a name for the control, such as SearchAmazon.

From the Toolbox, add a TextBox control, two Button controls, and a PictureBox control to the SearchAmazon control, as shown in Figure 9.

Figure 9. Add controls to the user control

Set the controls' properties as shown in Table 1.

Table 1. Properties for TextBox, Button, and PictureBox controls

You could use a ListBox control in the Visual Studio 2005 Tools for Office Second Edition add-in just as I did in the VBA add-in. It would be useful, though, to store all the information you need in the control for later use rather than keeping the XML document open. It may take longer to load initially, but after the user sees the filled custom task pane the response is good.

If all the data is text, you could still use the ListBox control. But another control, the DataGridView control, can display and store other types of information too. With the DataGridView control, you can enhance the add-in by displaying an image of the book in addition to the text-based information (that is the purpose of the PictureBox control). The DataGridView control also makes it easy to hide columns by setting the Visible property.

To add a control for the returned data, add a DataGridView control to SearchAmazon. Name the DataGridView control BookGrid. To add columns to the data grid, right-click the control and then click Add Column. Use the Add Column dialog box, shown in Figure 10, to add the columns listed in Table 2.

Figure 10. Add columns to the DataGridView control

Table 2. Columns for the DataGridView control

Use the DataGridView Tasks panel, shown in Figure 11, to disable adding, editing, and deleting in the data grid.

Figure 11. Disable edits in the BookGrid control

You can use many different properties to customize the look of the DataGridView control. To follow the example, change the default properties of BookGrid, as shown in Table 3.

Table 3. Property changes for BookGrid

Visual Studio makes it very easy to work with Web services by using SOAP. You add a Web Reference to the project, and Visual Studio builds all the classes you need to work with the Web service as an object model.

1. On the Project menu, click Add Web Reference.

2. In the Add Web References dialog box, type the path of the Amazon ECS WSDL:

   http://webservices.amazon.com/AWSECommerceService/AWSECommerceService.wsdl

3. Click Go.

4. Change the Web Reference Name to AmazonWS, as shown in Figure 12.

   Figure 12. Assign a name to the Web service

   

5. Click Add Reference.

In VBA, I used a constant to store my Amazon Access Key ID. Visual Studio 2005 provides a useful area for data like the Access Key ID: Projectsettings.

1. Right-click the project in Solution Explorer, and then click Properties.

2. Click the Settings tab.

Use the grid to add information for the Access Key ID, as shown in Figure 13.

Figure 13. Add an entry for your Amazon Access Key ID

After a value is stored in the project's settings, you can use it in your code through the My.Settings collection (in Visual Basic) or the Properties.Settings collection (in C#).

Now that the user interface is in order and the Access Key is available, it is time to start writing code. You can get to the code for the user control by right-clicking the control in the designer, and then clicking View Code.

You need to use a .NET Framework class to get an image for each book. Add code to the Declarations section to import the following two namespaces.

Imports System.Net
Public Class SearchAmazon

using System.Net;
namespace AmazonAddInWord
{
    public partial class SearchAmazon : UserControl

Next, add code to the SearchButtonClick event handler to send the SOAP request to the Amazon ECS. When you added the Web reference for the Amazon ECS, Visual Studio built a class named AmazonWS. You can use that class and its members to tell the Amazon ECS what you want to see in the response and make the request.

Dim amazonRequest As New AmazonWS.ItemSearchRequest
Dim amazonRequestSearch As New AmazonWS.ItemSearch
Dim amazonResponse As AmazonWS.ItemSearchResponse
Dim amazonItems As AmazonWS.Item() = Nothing
With amazonRequest
    .Keywords = """" & Me.KeywordTextBox.Text & """"
    .ResponseGroup = New String() {"Medium"}
    .SearchIndex = "Books"
End With
With amazonRequestSearch
    .AWSAccessKeyId = My.Settings.AmazonDevKey
    .Request = New AmazonWS.ItemSearchRequest() {amazonRequest}
End With
amazonResponse = My.WebServices.AWSECommerceService.ItemSearch( _
    amazonRequestSearch)
If amazonResponse Is Nothing Then
    MsgBox("No books found.")
    Exit Sub
Else
    amazonItems = amazonResponse.Items(0).Item
End If

AmazonWS.AWSECommerceService aws = new AmazonWS.AWSECommerceService();
AmazonWS.ItemSearchRequest amazonRequest = 
    new AmazonWS.ItemSearchRequest(); 
AmazonWS.ItemSearch amazonRequestSearch = 
    new AmazonWS.ItemSearch(); 
AmazonWS.ItemSearchResponse amazonResponse = new
   AmazonWS.ItemSearchResponse();
AmazonWS.Item[] amazonItems;
amazonRequest.Keywords = 
   "\"" + this.KeywordTextBox.Text + "\"";
amazonRequest.ResponseGroup = new string[]{"Medium"};
amazonRequest.SearchIndex = "Books";
amazonRequest.Sort = "salesrank";
amazonRequestSearch.AWSAccessKeyId =
    Properties.Settings.Default.AmazonDevKey;
amazonRequestSearch.Request = 
    new AmazonWS.ItemSearchRequest[] {amazonRequest};
amazonResponse =  aws.ItemSearch(amazonRequestSearch);
amazonItems = amazonResponse.Items[0].Item;

In context, the code to determine the number of items returned and iterate through them looks like this. For now, I commented out calls to two other procedures. BuildResults adds a row to the DataViewGrid and ShowImage displays an image of the selected book in the PictureBox control.

Private Sub SearchButton_Click(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles SearchButton.Click
' Code from the previous example is not reproduced here. It is 
' represented by the three dots.
.
.
.
    Me.BookGrid.Rows.Clear()
    Dim booksFoundCount As Integer
    If amazonItems.Length > 10 Then
        booksFoundCount = 10
    Else
        booksFoundCount = amazonItems.Length
    End If
    For i As Integer = 0 To booksFoundCount - 1
'        BuildResult(amazonItems(i))
    Next i
    If Not Me.BookGrid.Visible Then
        Me.BookGrid.Visible = True
    End If
'    ShowImage(Me.BookGrid.Rows(0))
End Sub

private void SearchButton_Click(object sender, EventArgs e)
{
// Code from the previous example is not reproduced here. It is 
// represented by the three dots.
.
.
.
    this.BookGrid.Rows.Clear();
    int booksFoundCount = 0;
    if (amazonItems == null)
    {
        MessageBox.Show("No books found");
    }
    else if (amazonItems.Length > 10)
    {
        booksFoundCount = 10;
    }
    else
    {
       booksFoundCount = amazonItems.Length;
    }
    if (booksFoundCount > 0)
    {
        for (int i = 0; i <= booksFoundCount - 1; i++)
        {
            BuildResult(amazonItems[i]);
        }
        if (this.BookGrid.Visible == false)
        {
            this.BookGrid.Visible = true;
        }
        ShowImage(this.BookGrid.Rows[0]);
    }
}

Most of the data in the data grid is text. You can use the properties of the AmazonWS class to get the string values returned by the Amazon ECS. The image is different, though. The XML response from the Amazon ECS sends a URL for each image. The code in BuildResult uses the HttpWebRequest object of System.Net to get the image as a stream. The code uses the stream to create a bitmap, which the code stores in BookGrid's Image column.

Private Sub BuildResult(ByVal bookItem As AmazonWS.Item)
    ' Get the book's smallest image.
    Dim imageURI As New Uri(bookItem.SmallImage.URL)
    Dim bookImage As Bitmap
    Dim imageRequest As HttpWebRequest = _
        DirectCast(WebRequest.Create(imageURI), HttpWebRequest)
    Dim imageResponse As HttpWebResponse = _
        DirectCast(imageRequest.GetResponse(), HttpWebResponse)
    Using stream As System.IO.Stream = imageResponse.GetResponseStream()
        bookImage = New Bitmap(stream)
        imageResponse.Close()
    End Using

    Dim title As String = bookItem.ItemAttributes.Title
    ' Build author from the list of authors.
    Dim author As String = BuildAuthor(bookItem.ItemAttributes.Author)
    Dim asin As String = bookItem.ASIN
    Dim publisher As String = bookItem.ItemAttributes.Publisher
    Dim publishDate As String = bookItem.ItemAttributes.PublicationDate
    Dim formattedPrice As String = _
        bookItem.ItemAttributes.ListPrice.FormattedPrice
            
    ' Add book's data to a row in the DataGridView.
    Dim row As New DataGridViewRow()

    Dim bookTitle As New DataGridViewTextBoxCell()
    bookTitle.Value = title
    row.Cells.Add(bookTitle)

    Dim bookAuthor As New DataGridViewTextBoxCell()
    bookAuthor.Value = author
    row.Cells.Add(bookAuthor)

    Dim bookASIN As New DataGridViewTextBoxCell()
    bookASIN.Value = asin
    row.Cells.Add(bookASIN)

    Dim bookPublisher As New DataGridViewTextBoxCell()
    bookPublisher.Value = publisher
    row.Cells.Add(bookPublisher)

    Dim bookDatePublished As New DataGridViewTextBoxCell()
    bookDatePublished.Value = publishDate
    row.Cells.Add(bookDatePublished)

    Dim columnBitmap As New DataGridViewImageCell()
    columnBitmap.Value = bookImage
    row.Cells.Add(columnBitmap)

    Dim bookPrice As New DataGridViewTextBoxCell()
    bookPrice.Value = formattedPrice
    row.Cells.Add(bookPrice)

    Me.BookGrid.Rows.Add(row)
End Sub

private void BuildResult(AmazonWS.Item bookItem)
{
    Uri imageUri = new Uri(bookItem.SmallImage.URL);
    Bitmap bookImage;
    HttpWebRequest imageRequest = 
        (HttpWebRequest) (WebRequest.Create(imageUri));
    HttpWebResponse imageResponse =
        (HttpWebResponse) (imageRequest.GetResponse());
    System.IO.Stream stream = imageResponse.GetResponseStream();
    using (stream)
    {
        bookImage = new Bitmap(stream);
        imageResponse.Close();
    }
    string title =
        bookItem.ItemAttributes.Title;
    string author = BuildAuthor(bookItem.ItemAttributes.Author);
    string asin = bookItem.ASIN;
    string publisher = bookItem.ItemAttributes.Publisher;
    string publishDate = bookItem.ItemAttributes.PublicationDate;
    string formattedPrice = 
        bookItem.ItemAttributes.ListPrice.FormattedPrice;
    DataGridViewRow row = new DataGridViewRow();

    DataGridViewTextBoxCell bookTitle = 
        new DataGridViewTextBoxCell();
    bookTitle.Value = title;
    row.Cells.Add(bookTitle);

    DataGridViewTextBoxCell bookAuthor =
        new DataGridViewTextBoxCell();
    bookAuthor.Value = author;
    row.Cells.Add(bookAuthor);

    DataGridViewTextBoxCell bookASIN =
        new DataGridViewTextBoxCell();
    bookASIN.Value = asin;
    row.Cells.Add(bookASIN);

    DataGridViewTextBoxCell bookPublisher =
        new DataGridViewTextBoxCell();
    bookPublisher.Value = publisher;
    row.Cells.Add(bookPublisher);

    DataGridViewTextBoxCell bookDatePublished =
       new DataGridViewTextBoxCell();
    bookDatePublished.Value = publishDate;
    row.Cells.Add(bookDatePublished);

    DataGridViewImageCell bookBitmap = 
        new DataGridViewImageCell();
    bookBitmap.Value = bookImage;
    row.Cells.Add(bookBitmap);

    DataGridViewTextBoxCell bookPrice =
        new DataGridViewTextBoxCell();
    bookPrice.Value = formattedPrice;
    row.Cells.Add(bookPrice);

    this.BookGrid.Rows.Add(row);
}

When the list of books appears, the task pane displays an image of the first book in the list. When the user moves from one row to another, the image changes. The DataGridView control raises a CellClick event when the user selects a row. The code for the CellClick event calls ShowImage, which copies the image for the selected row to DisplayImage, the PictureBox control.

Private Sub BookGrid_CellClick( _
    ByVal sender As Object, _
    ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
    Handles BookGrid.CellClick

    ShowImage(Me.BookGrid.Rows(e.RowIndex))
End Sub
Private Sub ShowImage(ByVal row As DataGridViewRow)
    Dim cellBitmap As DataGridViewImageCell = row.Cells(5)
    Me.DisplayImage.Image = cellBitmap.Value
    If Not Me.DisplayImage.Visible Then
        Me.DisplayImage.Visible = True
        Me.AddButton.Visible = True
    End If
End Sub

private void BookGrid_CellClick( 
    object sender, DataGridViewCellEventArgs e)
{
    ShowImage(this.BookGrid.Rows[e.RowIndex]);
}
private void ShowImage(DataGridViewRow row)
{
    DataGridViewImageCell cellBitmap = 
        (DataGridViewImageCell) (row.Cells[5]);
    this.DisplayImage.Image = (Image) (cellBitmap.Value);
    if (this.DisplayImage.Visible == false)
    {
        this.DisplayImage.Visible = true;
        this.AddButton.Visible = true;
    }
}

So far, the code for the Amazon add-in for Word is identical to the Amazon add-in for Excel. The only difference is in what happens when the user clicks Add. That code is specific to the host application.

To make it easier to reuse the BookGrid user control in another add-in, you can add a procedure (named InsertBook in the example) with the application-specific code to the ThisAddIn class. Then call InsertBook from the AddButtonClick event handler, sending current row from BookGrid as an argument.

Private Sub addButton_Click(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles AddButton.Click
    Globals.ThisAddIn.InsertBook( _
     BookGrid.CurrentRow())
End Sub

private void AddButton_Click(object sender, EventArgs e)
{
    Globals.ThisAddIn.InsertBook(BookGrid.CurrentRow);
}

Open the ThisAddIn class and add the InsertBook procedure that is appropriate for the host (Word 2007 or Excel 2007). The Word add-in's InsertBook procedure, stored in the ThisAddIn class, looks like the following code.

Public Sub InsertBook(ByVal row As DataGridViewRow)
    Dim rng As Word.Range = _
        DirectCast(Me.Application.Selection.Range, Word.Range)
    With rng
        .InsertAfter(row.Cells(1).Value & " (" & _
            row.Cells(4).Value.ToString.Substring(0, 4) & "). ")
        .Collapse(Word.WdCollapseDirection.wdCollapseEnd)
        .InsertAfter(row.Cells(0).Value)
        .Font.Italic = True
        .Collapse(Word.WdCollapseDirection.wdCollapseEnd)
        .InsertAfter(". " & row.Cells(3).Value.ToString)
        If Right(row.Cells(3).Value.ToString, 1) <> "." Then
            .InsertAfter(".")
        End If
        .Font.Italic = False
        .InsertParagraphAfter()
        .Collapse(Word.WdCollapseDirection.wdCollapseEnd)
        rng.Select()
    End With
End Sub

public void InsertBook(DataGridViewRow row)
{
    object collapseDirection = Word.WdCollapseDirection.wdCollapseEnd;
    string publishDate = (string) (row.Cells[4].Value);
    string publishYear = publishDate.Substring(0,4);
    try
    {
        Word.Range rng = (Word.Range)(this.Application.Selection.Range);
        rng.InsertAfter(row.Cells[1].Value.ToString() + " (" +
            publishYear + "). ");
        rng.Collapse(ref collapseDirection);
        rng.InsertAfter(row.Cells[0].Value.ToString());
        rng.Font.Italic = -1;
        rng.Collapse(ref collapseDirection);

        string publisher = row.Cells[3].Value.ToString();
        rng.InsertAfter(". " + publisher);
        if  (publisher.EndsWith(".") == false)
        {
            rng.InsertAfter(".");
        }
        rng.Font.Italic = 0;
        rng.InsertParagraphAfter();
        rng.Collapse(ref collapseDirection);
        rng.Select();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, ex.Source,
            MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

In Excel, InsertBook looks like the following code.

Public Sub InsertBook(ByVal row As DataGridViewRow)
    Try
        Dim rng As Excel.Range = _
            DirectCast(Me.Application.Selection, Excel.Range)
        rng.EntireRow.Insert()
        With Me.Application
            Dim intRow As Integer = .ActiveCell.Row
            Dim intColumn As Integer = .ActiveCell.Column
            .Cells(intRow, intColumn) = row.Cells(0).Value
            .Cells(intRow, intColumn + 1) = row.Cells(1).Value
            .Cells(intRow, intColumn + 2) = row.Cells(3).Value
            .Cells(intRow, intColumn + 3) = row.Cells(4).Value
            .Cells(intRow, intColumn + 4) = row.Cells(6).Value
        End With
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, ex.Source)
    End Try
End Sub

public void InsertBook(DataGridViewRow row)
{
    try
    {
        Excel.Application excelApp = this.Application;
        Excel.Range rng = (Excel.Range)(excelApp.Selection);
        rng.EntireRow.Insert(missing, missing);
        int rowNumber = excelApp.ActiveCell.Column;
        int colNumber = excelApp.ActiveCell.Column;

        excelApp.Cells[rowNumber, colNumber] = row.Cells[0].Value;
        excelApp.Cells[rowNumber, colNumber + 1] = row.Cells[1].Value;
        excelApp.Cells[rowNumber, colNumber + 2] = row.Cells[3].Value;
        excelApp.Cells[rowNumber, colNumber + 3] = row.Cells[4].Value;
        excelApp.Cells[rowNumber, colNumber + 4] = row.Cells[6].Value;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, ex.Source,
            MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Now that you have the user interface for the add-in, you can change the event handler for the command button to open a new custom task pane that contains the SearchAmazon control. You do this by adding an item to the ThisAddIn class's CustomTaskPanes collection. The CustomTaskPanes collection's Add method requires a UserControl object and a name for the new task pane. To add the user control to the task pane and make it visible, add the following code to the OpenTaskPaneButtonClick event handler.

Private Sub OpenTaskPaneButton_Click( _
    ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, _
    ByRef CancelDefault As Boolean) _
    Handles OpenTaskPaneButton.Click

    Dim mySearch As New SearchAmazon
    Me.CustomTaskPanes.Add(mySearch, "Search Amazon").Visible = True
End Sub

private void OpenTaskPaneButton_Click( 
    Office.CommandBarButton Ctrl, ref bool CancelDefault)
{
    SearchAmazon mySearch = new SearchAmazon();
    this.CustomTaskPanes.Add(mySearch, "Search Amazon").Visible = true;
}

The add-in is now ready to go. When you build and run the project, the Search Amazon button appears on the Add-Ins tab as before. Click it to see the new custom task pane. After doing a search, the custom task pane looks like Figure 14.

Figure 14. The completed custom task pane

Using Visual Studio 2005 Tools for Office Second Edition to create add-ins for Office applications is easier than using previous versions of Visual Studio. If you have written add-ins in VBA or Visual Basic 6, now is the time to take a close look at migrating some or all of them to managed code, by using Visual Studio 2005 Tools for Office Second Edition. Not only will you get the benefits of Visual Studio and the .NET Framework, but you will be able to create a richer user interface, particularly if you are also moving to the 2007 Microsoft Office system. It will take some time to learn about Windows Forms controls and new coding techniques that use the .NET Framework, but the development environment, Windows Forms control palette, and ability to program the task pane make the time you spend learning worthwhile.

Jan Fransen is a writer, trainer, and consultant who specializes in Microsoft products. As a writer, Jan has developed training courseware for AppDev, contributed to books on Microsoft Office, written white papers for publication on MSDN, and created samples designed to help developers get up to speed quickly on new Microsoft products and features.

To learn more about the products and technologies mentioned or used in this article, see these resources:

• Amazon Web Services Discussion Forums

• Migrating a Shared Add-in to a Visual Studio 2005 Tools for the Office System SE Add-in

• Office Development with Visual Studio Developer Portal

• Microsoft Visual Studio Developer Center

• Documentation: Visual Studio Tools for Office

• Blog: Office Development with Visual Studio

• Forum: Visual Studio Tools for Office

• Sample Chapter: Writing Secure Code

• Microsoft Security Developer Center