Accessing Web Services in Excel Using Visual Studio Tools for the Microsoft Office System

 

Geoff Hill
Developer Services, Microsoft UK

April 2004

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Excel 2003
    Microsoft Visual Basic® .NET 2003

Summary: Demonstrates how to integrate a stock information Web service into an Excel 2003 worksheet using Visual Studio Tools for the Microsoft Office System. (9 printed pages)

Contents

Introduction
Prerequisites
Creating an Excel Project in Visual Studio .NET
Adding the Web Reference
Preparing the Excel Worksheet
Coding the OfficeCodeBehind
Execution
Conclusion

Introduction

Developers now have a very powerful tool available to them for use in creating smart client applications for Microsoft® Office 2003. I'm talking about Microsoft Visual Studio® Tools for the Microsoft Office System. This add-in to Microsoft Visual Studio .NET 2003 allows a developer to create managed DLLs for specific Office 2003 templates quickly, with a minimum of effort. A created DLL is then hooked into a certain Microsoft Office Excel or Microsoft Office Word file. When that file is opened, the DLL is loaded seamlessly into the background. The developer now can use the Microsoft .NET Framework classes, along with the Excel or Word object model, to create powerful smart client applications.

Now a person developing an Office template can fully harness the Visual Studio .NET integrated development environment (IDE) and its powerful debugger. The added benefit of developing in managed code means the developer has more control over memory, typecasting, and so on. In this article, I go through the steps for accessing a stock Web service and hooking this Web service into an Excel 2003 application. (I don't go over what to do with that data; you can use your imagination there!) A reasonable knowledge of Microsoft .NET Framework programming is assumed.

Prerequisites

First, you have to make sure that all the right parts are installed. I'm not going to go into detail about that here, but make sure to install everything in the correct order in order for it to work properly. For more information, see How to: Install Visual Studio Tools for the Microsoft Office System.

Creating an Excel Project in Visual Studio .NET

I am doing this development in Microsoft Visual Basic® .NET instead of Microsoft Visual C#® for several reasons. First, Visual Basic .NET supports optional parameters and C# does not. That means that C# developers must supply values for every optional parameter in an Office method. Visual Basic .NET developers only have to supply parameters for values they need. In addition, C# developers have to use different accessor methods to ones that Visual Basic .NET developers do, because C# doesn't support parameters for properties (indexers are the exception.) Being as how I hate long-winded code, I'll opt for using Visual Basic .NET then.

In Visual Studio .NET, create a new Visual Basic Office project for Excel.

To create a new Excel Workbook project

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

    The New Project dialog box appears.

    Figure 1. The New Project dialog box in Visual Studio .NET

  2. In the Project Types pane, expand Microsoft Office System Projects, and then select Visual Basic Projects.

  3. In the Templates pane, select Excel Workbook.

  4. Name the project, and store it in a convenient local path.

    I called mine WebServiceStockExcelProject, but that may be a bit of a mouthful. Call it whatever you like.

  5. Accept the defaults in the Microsoft Office Project Wizard, and click Finish to create the project.

Notice that the autogenerated code has already named your class as OfficeCodeBehind. Don't bother changing that. It also gives you access to two friend objects, ThisWorkbook and ThisApplication. You use these to access the Excel application when the time comes. Because of the hooks in the managed code, when the Office application starts, the code starts automatically with it. And when the Office application shuts down, it shuts down gracefully (so you don't have to worry about marshaling any COM objects).

Adding the Web Reference

First you add the Web reference to the Project References folder.

To add a Web reference

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

  2. You're going to be using the free Web service, http://www.nexus6studio.org/Services/StockQoute.asmx?WSDL, in your code. Be aware that it is a free service and may not be available at the time of reading. Place it here and click Go:

    Figure 2. Adding a Web reference

    You're only going to be concerned with GetDetailQuote and the structure DetailQuote that it returns.

  3. Click Add Reference.

    You now have a reference to these two Web services.

Preparing the Excel Worksheet

When the Microsoft Office Project Wizard creates the initial code-behind files for the project, it also creates an Excel worksheet in the working directory. You should add this item to the project for easy reference.

Open the worksheet up, and make it look similar to Figure 3.

**Note   **A warning might appear stating that the assembly name or Assembly Link Location property is corrupted, because you have not compiled the assembly yet. Click OK to close the warning.

Figure 3. The Excel worksheet

The only tricky part here is the forming of the hyperlink Get Stock Information (the rest is straight formatting). The hyperlink is important because it kicks off the Web service. This takes several steps:

To create a hyperlink in the worksheet

  1. On the Insert menu, point to Name, and then click Define.
  2. Define a named range as Get_Stock and define it as range =Sheet1!$A$6.
  3. On the Insert menu, click Hyperlink to open the hyperlink creation window.
  4. On the left side, click Place in This Document, select Get_Stock as the named reference, and click OK.
  5. Save and close the workbook.

Coding the OfficeCodeBehind

Here's the fun part, the part you've been waiting for. You now get to use the Excel object model to place and format the Web service values.

So, to begin with, make an event handler that handles a click on the hyperlink you made in the application. Place this subroutine just below the ThisWorkbook_BeforeClose subroutine:

Private Sub ThisApplication_SheetFollowHyperlink _
    (ByVal Sh As Object, ByVal Target As Excel.Hyperlink) _
    Handles ThisApplication.SheetFollowHyperlink

        If Target.SubAddress = "Get_Stock" Then
            GetStock(Sh)
        End If

End Sub

This subroutine executes upon any click of any hyperlink in the application. It passes two arguments, one of which is Sh,**which represents the Excel worksheet. The other is Target, which represents the actual hyperlink.

Remember how you named the hyperlink range to Get_Stock? You now check to see if that hyperlink was clicked by checking the Target.SubAddress attribute. If that's the case, you're in business, and you call the GetStock subroutine, passing the Sh sheet object variable.

Now you code up the GetStock subroutine. Place this below the ThisApplication_ SheetFollowHyperlink subroutine that was previously coded.

    Private Sub GetStock(ByVal Sh As Object)
        Dim sheet As Excel.Worksheet = CType(Sh, Excel.Worksheet)
        Dim stockSnapshot As New _   
            org.nexus6studio.www.Nexus6StudioStockQuote
        Dim snapShot As org.nexus6studio.www.DetailQuote
        Dim symbol As String = sheet.Range("F6").Value

        ' This is the error message area.
        sheet.Range("A21:H32").Merge()

        Try
            snapShot = stockSnapshot.GetDetailQuote(symbol)
        Catch ex As Exception
            ' You can use the Range class above, or the Cells class below.
            ' For the Range class, you have to set the Value property.
            ' See the examples after the Try block.
            ' Both of these are the same:
            ' sheet.Range("A21").Value = ex
            sheet.Cells(21, "A") = ex

        End Try

        sheet.Range("E10").Value = snapShot.Bid
        sheet.Range("E11").Value = snapShot.Ask
        sheet.Range("E16").Value = snapShot.Open
        sheet.Range("F14").Value = snapShot.High
        sheet.Range("F18").Value = snapShot.Low
        sheet.Range("F16").Value = snapShot.Price
        sheet.Range("F16").Font.Bold = True
        sheet.Range("F16").Font.Underline = True
        sheet.Range("E12").Value = snapShot.Change_Points

    End Sub

As you might notice, the Excel interop represents the current worksheet as a generic object. Since you cannot work easily with a generic object within your code, you then instantiate an object of class Excel.Worksheet. And since you're unboxing this "sheet" object, you must explicitly convert it using CType.

You then must instantiate the Web service itself and the struct that the Web service returns. The instantiated Web service object is stockSnapshot, and the instantiated struct is snapShot. Finally, you get the stock symbol. As you can see in Figure 3, the cell "F6" is designated to be the location to put the symbol.

Next, you designate sheet range A21 to H32 as a merged range, so you can place any exceptions you catch there.

Now, since you don't have any idea what might happen if the call to the Web service fails, I would put it in a Try/Catch block. Within the Try block, call the Web service, pass in the symbol, and get back the snapshot struct containing the relevant stock values for the day.

**Note   **There are various ways one can access cell values. As you can see, you can either access them via the Range object or the Cells object. I coded in the use of the Cells object in the Catch block to show the syntax, but I prefer using the Range object. In my opinion, it is a more flexible object to manipulate the cell ranges with.

Finally, the last lines of the subroutine place the struct values into the spreadsheet. The values of the struct are all defined as strings, yet you don't do any explicit conversion of them before putting them into the spreadsheet. That's because Excel does this for you with any number passed into its Value property.

I also added two lines to make the current stock value stand out; I set the Font.Bold and Font.Underline properties to True.

Execution

Shall we run this now? Compile the code you just wrote, and then go to the folder you saved the project in. Activate the Excel file therein. You should get the Excel sheet that you created. Since I'm curious how Microsoft shares are doing now, let's enter MSFT (or lowercase, it doesn't matter) in cell F6, press ENTER, and click the Get Stock Information hyperlink. You should get something similar to Figure 4:

Figure 4. Web service results

Ah rats, the price could be higher...

Notice the region A21 to H32? That's now one big merged region, exactly how you defined it in the code above. It's ready for any anomalies to be caught.

Conclusion

As you can see, binding Web services into an Excel 2003 application using Visual Studio Tools for the Microsoft Office System can be a snap now. This opens up a whole world of opportunities for those of us who like to trade the markets. We can now download fresh data into our spreadsheets seamlessly in the background, and we can then use the power of Excel to manipulate this data as we need. All that's missing is a crystal ball!