14.4 Using Web Services


14.4 Using Web Services

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 google.com are currently testing web services and offering test accounts access to these services. Thus, for example, on the basis of web services one 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):

  • http://msdn.microsoft.com/ webservices /

  • http://www.amazon.com/webservices

  • http://www.google.com/apis/

  • http://www.learnxmws.com/

Web Services Toolkit 2.0

Since Office 2002 and Excel 2002 are not yet .NET compatible, the use of web services is not yet envisioned . However, at it web site, Microsoft offers a free extension package to Office 2002, namely, the Web Services Toolkit. With the aid of this toolkit it is practically child's play to make use of web services via VBA code. (http://msdn.microsoft.com/downloads/)

Tip  

Although the toolkit bears the name Web Services Toolkit for Excel 2002, it can apparently be used for Excel 2000 as well. In any case, the installation was accomplished seamlessly, and the example presented in this section, as well as the web services dialog ( 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 installation and a restart of Excel, you can create links via ToolsReferences to the new libraries Microsoft Soap Type Library 3.0 and Microsoft XML 4.0 . These libraries are used with the web services for data exchange.

Moreover, in the VBA development environment there is available a new command, ToolsWeb Services References. With this command you can search for a web service in the Internet. If the search is successful and the offered web service is described by an ASMX or WSDL page, then it can be inserted into the current VBA project with the Web Service References dialog program code. In this process, data structures and classes are generated that make the use of web services quite simple. At the same time, references to both of the above-named libraries are set up.

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.

ASMX

Unknown abbreviation

File identifier for .NET web service files

HTTP

Hyper 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 Object 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)

WSDL

Web Service Description Language

Text format based on XML for describing the services and data types of a web service; the WSDL file describes the syntax of the offered methods , that is, the parameters, return value 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 in 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 services)

  • http://www.xmethods.com/

  • http://www.salcentral.com/

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 only, of course, as long 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 centers on a list of ISBN numbers in column B and 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.

click to expand
Figure 14-6: Web service example application

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 ToolsWebservice 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.

click to expand
Figure 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_GetAmazonSalesRank , wsm_GetAmazonUKSalesRank , wsm_GetAmazonDESalesRank , etc., the various services of the web service are called. With Set objvar = Nothing the connection is severed.

  • struct_All, struct_Prices , struct_SalesRankNPrice1 , and struct_SalesRanks : These classes make available simple data structures, which are used by various wsm_GetXxx methods to return results.

    The construction of these classes is quite simple. In the example program the class struct_SalesRankNPrice1 consists simply of the two instructions: 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_GetXxx methods; that is, you do not yourself have to worry about this transformation.

Even if you develop your own web services, 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).

Applications of the Classes

The application of classes generated by the web services dialog is rather simple: By creating an object of the class clsws_SalesRankNPrice , you create a connection to the web service. You may then use all the methods of the web service as though they were ordinary VBA functions. In the example that follows the sales rank of the book with ISBN number 1893115577 is returned via wsm_GetAmazonSalesRank . To close the connection, just set the object variable ws to the value Nothing .

 Dim ws As clsws_SalesRankNPrice Dim result As String ' create connection to the web service Set ws = New clsws_SalesRankNPrice ' call a method of the web service result = ws.wsm_GetAmazonSalesRank("1893115577") MsgBox "SalesRank: " + result ' close the connection Set ws = Nothing 

Example Program

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 with the initialization of the Range variable 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 are determined with Offset and cleared. After the connection to the web service has been established, a loop runs through all the cells c of the range r .

From c a character string of ISBN numbers is returned. The two cases are necessary because ISBN numbers can be stored in a worksheet either as numbers or as character strings (for example, in order to represent ISBN numbers that begin with 0). The web services method wsm_GetAmazonSalesRankNPrice returns an object of the class struct_SalesRankNPrice1 , whose two elements SalesRank and Price are written into the cells adjacent to c.

 ' webservice.xls, Module sheet1 Private Sub  CommandButton1_Click  ()   Dim i As Integer   Dim r As Range, c As Range   Dim isbn As String, s As String   Dim SalesWebserv As clsws_SalesRankNPrice   Dim result As struct_SalesRankNPrice1   ' feedback   Application.DisplayStatusBar = 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 = Worksheets(1).Range(Range("b2"), Range("b2").End(xlDown))   ' 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_code:   If Err Then MsgBox ("error: " + Err.Description)   ' close connection   Set SalesWebserv = Nothing   Application.StatusBar = False End Sub 



Definitive Guide to Excel VBA
Linux Application Development For The Enterprise (Charles River Media Programming)
ISBN: 1584502533
EAN: 2147483647
Year: 2002
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net