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
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
In the Project Types pane, expand Microsoft Office System Projects, and then select Visual Basic Projects.
In the Templates pane, select Excel Workbook.
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.
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
On the Project menu, select Add Web Reference.
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.
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
- On the Insert menu, point to Name, and then click Define.
- Define a named range as Get_Stock and define it as range =Sheet1!$A$6.
- On the Insert menu, click Hyperlink to open the hyperlink creation window.
- On the left side, click Place in This Document, select Get_Stock as the named reference, and click OK.
- 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!