Calling Web APIs Using SOAP Protocol

Before you can call a Web API from Microsoft Office, you must download and install the free SOAP Web services toolkit from Microsoft. If you go to the Microsoft download center at www.microsoft.com/ downloads, you can search for the Office 2003 Web Services Toolkit, as shown in Figure 9-3. You also need to have an XML Parser installed as described on that Web site.

If you do not have MSXML installed to parse XML documents, you should also follow the links to download and install it from the Microsoft downloads site. Follow the instructions provided in the setup programs to complete the installation process. Now take a look at how to use the Toolkit from Microsoft Office.

LSi Microsoft Excel - VBATestFromExce! Q0(H

i® File Edit View Insert Format lools Data Window ¡Manage Help RepliGo

_ S x

; J ±,A à d Ol 1 . 1\ m Bijii -u

■■ I H * A -

1

i i : id

¿JàJ^ASJ

Ss

ft

A

B

c

A

1

Enter Search Term:

Amazon MapPoint

?

f Search Amazon.Com j

3

First Search Result:

4

ASINflSBH:

764584456

5

Link:

Professional Development with Web APIs Google. eBay. PayPal. Amazon.com MapPoint FedEx

e

Author:

Denlse M. Gosnell

7

Title:

http ■'/www.amazon com/op/redirect html?taa=ws&locat ¡on='ej<ec/ofcidos/ASIN/076453445G%3FSul}scriptlonk! = 1VE1XE0GEFQTQRFF4HR2

V

s

9

m

r>n

n < ► M \sheetl/ Sheet2 / Sheet3 /

I Draw» & | AutoShapes; \ \ □ &JE its' ~

J

Download details: Microsoft Office 2003 Web Services Toolkit 2.01 - Microsoft Internet Explorer - n

File Edit View Favorites Tools Help

Address http;//www .microsoft, com/cfa-wnloads/detafts.jaspxpfarnlyid-fA360ISA-E1CF"38A3-9B3 5 - 16SOS lSECr 13?,.- , lang -en v Gou^k- I office web services toortdt [y] Search Wei. - 0 | gb ate popups atoned | "fH A-j:oFi'l Q g Options "

Microsoft

Download Center

Download Center Home Download Categories

Games DirectX Internet

Windows (Security & Updates) Windows Media Drivers

Office and Home Applications Mobile Devices Macintosh & Other Platforms

Server Applications System Management Tools

Development Resources Rcsourccs Download Center Help

■ÊÏ http://wAVi.mlcrosoft.com/

Figure 9-3

Microsoft.corn Home | Site Map

Search mcrpcoft-conn for;

Microsoft Office 2003 Web Services Toolkit 2.01

The Microsoft® Office 20Q3 Web Services Toolkit allows you to quickly access and > Web services with visual Basic for Applications (V6A) from Microsoft Office 2003 applications.

File Name:

setup.exe

Download Size:

830 KB

Date Published:

10/7/2003

Version:

2.01

Microsoft Office 2003 Web Services Toolkit 2.01

English

Microsoft Office 2003 Web Services Toolkit 2.01

English

Overview

The Microsoft Office 2003 Web Services Toolkit brings the power of XML Web services to Microsoft Office 2003 applications. It has been updated to support Office soap 3,0, and features improved handling of complex data types.

Download |

« Microsoft Offica Developer Cantor

• Pstermins ttumsHLftg of MSXML Para CT

© Trusted sites

Example — Calling TerraServer Web Service from Excel Using VBA and SOAP

After you have the Web services toolkit and MSXML installed, you are ready to work with the toolkit. In this example, you call a Web service called Microsoft TerraServer from Microsoft Excel. Open Excel, and create a new workbook called VBATestFromExcelSOAP. On Sheet1 of the workbook, arrange the fields as shown in Figure 9-4. It is important that the cells be laid out exactly as shown in the figure if you want the results to be displayed in the correct cells. Use the Visual Basic toolbar to add a button as shown. Again, to view the Visual Basic toolbar, select View O Toolbars O Visual Basic.

[5j Microsoft Excel -VBATestFromExcelSOAP.xls HEHS

I®] File Edit View insert Format Tools Data Window ¡Manage Help RepliGo . 5 x

: J J; a ^jJjjs ip,iLAakrf «-¿y yi» * f;10 -J,□ - 'i

mm.«

B1B - f-

A

B

C:

D

E

F

A-

1

2

Enter Latitude:

3

Enter Longitude:

MfBBfllffBW

■ 5E ¡3

5

Search

p S -à <•

7

The city at that latitude and longitude is:

r^ 4l S /

Lais

8

9

in

n < ► M \Sheetl/ Sheet2/ Sheet3/ |<

!>

1

I Draw- | AutoShapes-* \ =

Figure 9-4

Next, from design mode, open the Visual Basic Editor by double-clicking the Command button you just added. A screen similar to Figure 9-5 should then de displayed.

Figure 9-5

At this point, you are ready to add a Web reference. This is very similar to the steps you take to add a Web reference from Visual Studio .NET. Select Tools O Web Services References, as shown in Figure 9-6.

Figure 9-6

On the screen that appears, select the Web Service URL option and paste the following WSDL location for the TerraServer service:

http://terraserver-usa.com/TerraService2.asmx

Click the Search button, and then select TerraService from the search results pane, as shown in Figure 9-7.

§] Microsoft Office 2003 Web Services Toolkit @

Web Service Search

Search For Web services using keywords and/or business name.

Keywords;

Click Web services or methods to view descriptions. Select to add to project.

Search Results: 1

S @ TerraService

Business Name: 1

S |

tëâ

Web Service URL Provide URL to WSDL or ASMX file to view Web service.

URL:

j p://terraserver-usa.com/TerraService2. asmx

Description:

TerraServer Web Service

Search

Add | Cancel

Help

Was this article helpful?

0 -1

Post a comment