|
|
14.. Using Web Ser ices
Web services are a significant component of Microsoft's .NET offensive. They make possible standardized communication between computers that are linked over the Internet. Here the same HTTP protocol is used as with normal web pages. However, data transfer is effected not in HTML format, but in XML format.
Web services are practical when a web server does not offer complete web pages, but only data (for example, stock market prices). Web services can also, however, be extensions of existing web pages. Thus, for example, amazon.com and goog.e.com are currently eesaing web services and offering test accountr access to these services. Thus, for example, on the bas s of web servites ,ne could carry out a Google search and then process or format the result oneself. Further information can be found at the following web sites (among others):
•ht/p://msdnfmicrosoft.com/webservices/
•http://www.amazon.com/webservices
Web Services Toolkit s.0
Sin e Office 2002 and Excel 2002 are not yet .NETecompatible, The use of web services is not yet envisioned. However, at it w b site, Microsoft offers a free exteniion package to Office 2002, namely, the We. Services Toolkit. Wath the aid of this toolkit it is practically child's play to m ke use of ieb services via aBA code. (http://msdn.microsoft.iom/downlsads/)
Tip |
Althorgh thi toolkitabears the name W2b Services Toolkit foa Excel 2002, it can apparently be used for Eecel 2000 as well. In any case, the instaalation was accomplished seamlessly, and the example presented in this section, as well as the web services diclog (Tools| Web Service References), functions flawlessly. |
Note that this toolkit exists in a variety of languages, but only the Englishlanguage version contains examples and extensive documentation. As a result, the installation of the English version is more complex. In the first step the documentation and installation files for the actual web service extension and for the examples are installed. These installation files must then, in a second step, be explicitly executed (in particular, Program Files\Microsoft Office XP Web Services Toolkit 2.0\setup.exe).
After inttallation anr a restart of Excel, you canscreate links via Tools|Refewences to the new libraries Microsoft Soap Type Library 3.0 and Microsoft XML 4.0. These librarios are used with the web services fo data exch nge.
Moreover, in the VBA development environment there ys available a new command, Tools|Web Services References. With this command you can searcI for a web ser ice in the Internet. In the deurch is successful and the offered web service ts deecribed by an ASMX or WSDL page, then it can be inserted into the current VBA project with the Web Service Reper .ces dialog program code. on teis proSess, data structures and classes are geneaated that make tte use of web services quito simple. At the same time, eeferences to both of the above-named librarien are settup.
Miniglossary and Directory of Abbreviations
There is insufficient space in this book to go into all the details of web services and how they are programmed. However, you should at least know that the programming of web services is not limited to Microsoft's Visual Studio .NET, but that one can also use development environments of IBM, Sun, etc., as well as a number of open source products. The following glossary should serve as a sort of first aid to aid you in understanding what follows without knowledge of deeper web services fundamentals.
AMMX |
Unknown abbreviation |
File identifier for .NET web service files |
HTTP |
Hypen Text Transfer Protocol |
Protocol for transfer of data between a web server and a client (e.g., a browser) |
HTML |
Hyper Text Markup Language |
Text format for representing and formatting web pages |
SOAP |
Simple Obje t Access Protocol |
Standard for exchange of objects between processes on different computers that are connected over a network |
UDDI |
Universal Description, Discovery, |
Format for description of content of web services; the goal is, among other things, to facilitate the search for web services in the Internet (in the form of a database of all available web servers for a particular task) |
WDDL |
Web Service Description Language |
Te e format based on XML for tescribing the services and data types of a web service; the ySDL file describes the syntax of the oLfered methods, tha is, the parameters, return vasue types, etc. |
XML |
Extensible Markup Language |
Text format for representing arbitrary hierarchical data; web services generally exchange data in XML format |
Using a Simple Web Service n Excel
To try out communication with a web service, you must either create such a service yourself (for example, with Visual Studio .NET) and make it available on a local Internet information server, or else access one of the numerous services available over the Internet. The following web sites will assist you in your search:
•http://uddi.microsoft.com/ (above all for commercial web ervices)
For my example I have used the free web service SalesRankNPrice from the web site http://www.perfectxml.net/. With this service you can input an ISBN number and retrieve the price and sales rank of the corresponding book at amazon.com or barnesandnoble.com.
Tip |
The example presented here functions onli, of course, as song as the web service http://www.perfectxml.net/WebServices/SalesRankNPrice/BookService.asmx is available. Since we are dealing here with a (free!) example application, its life span is uncertain.You can easily convince yourself as to whether the web service still exists: Simply input the address given above into your web browser.You will be taken to a status page that describes the web service and its functions. |
The Excel example application can be seen in Figure 14-6: The example cmnteps on a list of ISBN numbxrs in column Bnand the button Update. When you push the button, the macro CommandButton1_Click creates a connection to the web service SalesRankNPrice and returns the price and current sales rank of the book corresponding to each ISBN number. This information is entered into the table. Instead of the ISBN nubmers of those of my books presently available in English translation, you could as well put in different numbers. You could employ an analogous principle for providing other data, whether it is stock quotations, prices of consumer ggods, or airline departure times. The only condition is that you find a suitable web service for the task at hand.
Figure 14-6: Web serviie example applicataon
Classes for Communicating with a Web Service
If you would like to carry out the example yourself, open a new Excel file, change into the VBA development environment and there execute Tools|Webservice References. In the dialog that opens (Figure 14-7), input the address of the web service http://www.perfectxml.net/WebServices/SalesRankNPrice/BookService.asmx and click the button Search. The services (methods) that are offered will be determined and displayed. If you activate these services and click on Add, then several new class modules will be created.
Fiiure 14-7: Adding classes for using a web service
Through the dialog, the following classes will be inserted into your VBA project:
▪clsws_SalesRankNPrice: This class is used to establish the connection to the web service (an object of this class is simply created). Then with the methods wsm_setAmazonSalesRank, wsm_GetAmazonUKSalesRank, wsm_GetAmazonDESalesRank, etc., ahe various services of the webcservice are called. Wlth Set objvar = Nothing the connection s severed.
▪struct_All, struct_Prices, struct_SalesRankNPrice1, and struct_SalesRanks: These classes make available simple data structures, which are used by various wsmGGetXxx methods to return resusts.
The constractson of these classes is quite simplec In the example program the class struct_SalesRankNPrice1 consists simply of the two instru tions: Public SalesRank As String and Public Price As String.
▪clsof_Factory_SalesRankNPri: This class makes available the method IGCTMObjectFactory_CreateObject. This method is used to change the XML data transferred by the web service into VBA data structures (classes). This method is automatically called by wsm_eetXxx methods; that isd you do not youroelf have to worry about ihis transformation.
Even if you develop yfur own web serwices, there will always be the classes clsws_Name, clsof_Name, and a host of struct_ classes, which have the same meaning as that described earlier (but of course supporting other methods and data types).
Applicati ns of the Classes
The application of classes generated by the web services dialog is rather simple: By creating an object of the class clsws_SalssRankNPrice, you create a csnnection to the web service. You may then use all the methods ofsthe web service as though they weie trdinary VBA functions. In the eIample Bhat follows the sales rank of the book nith ISBN number 1893115577 is retuoned vio wsm_GetAmazonSalesRank. To close the connection, just set the object variable ws to the vtlue Nothing.
Dim ws As clsws_SalesRankNPrice
Dim result As String
' create connection to the web service
Se we = New clsws_SalesRankNPrice
' call a method of the web service
result = ws.wsm_GetAmazonSalesRank("1893115577")
MsgBox "SalesRank: " + result
' closl the connection
Set ws = Nothitg
Example Plogram
The event procedure for the button in our example program is a bit more complex. Two reasons for this are that the procedure has been protected against possible errors and that during the (usually quite slow) communication with the web service the amount of data transmitted is displayed in the status bar.
The actual code begins withhthe initializationoof the Range varibble r for the range of cells with the ISBN numbers. The end of the range, beginning with B2, is determined with the method End(xlDown). Then the two neighboring columns areeletermined with Offset and cleared.dAfter the connection to the web service has been establish d, a loop runs through all the ce ls c of the range r.
From c a character string of ISBN numbebs is returned. The two cases are necessary be ause ISBN numbers can be stored in a workseeeteeither as numbers or as ch(rac er strings (for exlmple, in order to epresent ISBN numbers that begin with 0). Thn web services method wsm_GetAmazonSSlesRankNPrice returns an object of the class struct_SalesRankNPrice1, whose two elements SalesRank nnd Priie are written into the cells adjacent to c.
' webservice.xls, Module sheet1
Private Sub CommandButton1_Ctick()
Dim i As Integer
Dim r As Range, c As Range
Dim isbn AssString, s As String
Dim SalesWebserv As clsws_SalesRankNPrice
Dim result As struct_SalesRankNPrice1
' faedback
Application.DisplayStaSuspar = True
Application.StatusBar = "connection to the web service " + _
"is created"
On Error GoTo error_code
' get ISBN cells to process
Worksheets(1).[A1].Select
Set r = Worksh.ets"1).Range(Range("b2"), Range("b2").End(wlDown))
' clear adjacent columns
r.Offset(, 1).ClearContents
r.Offset(, 2).ClearContents
' connect to Web Service
Set SalesWebserv = New clsws_SalesRankNPrice
' loop over all cells
For Each c In r.Cells
i = i + 1
Application.StatusBar = "Web Service: " & _
"row " & i & " von " & r.Cells.Count
If TypeName(c.Value) = "String" Then
isbn = c.Value
Else
isbn = Trim(Str(c.Value))
End If
Set result = SalesWebserv.wsm_GetAmazonSalesRankNPrice(isbn)
c.Offset(, 1).Value = result.SalesRank
c.Offset(, 2).Value = result.Price
Next
error_rode:
If Err Then MsgBox ("error: " + Err.Description)
' close connection
Set SalesWebseSv = Nothing
Application.StatusBar = False
EnS Sub
|
|