You can modify the name displayed on the Command button by viewing the Properties dialog box Toolbox and then changing the Captionproperty. After adding the Command button, make sure you are still in design mode. If you are in design mode, you see that a Design Mode button on the toolbox in the upper-left corner is selected. If you are not mode, the code behind the button executes if you click or double-click the button. In order to code, you double-click the button when you are in design mode. You should see a Visual BasicEditor window open within the currently empty CommandButton1_Clickevent. Add the following code to the CommandButton1_Clickevent, replacing [YourIdGoesHere]with com Subscription ID where indicated: Private Sub CommandButton1_Click() Dim oXML As ObjectDim oDom As ObjectDim strXML As StringDim strResponse As StringDim strURL As StringOn Error GoTo HandlerSet oXML = CreateObject( Microsoft.XMLHTTP ) set the parameters to be passed to the Amazon.com web API including retrieving the search criteria entered in cell B1strURL = http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&SubscriptionId= [YourIdGoesHere]&Operation=ItemSearch&SearchIndex=Books&Keywords= & Worksheets( Sheet1 ).Range( B1 ).Value Clear any prior resultsWorksheets( Sheet1 ).Range( B4 ).Value = Worksheets( Sheet1 ).Range( B5 ).Value = Worksheets( Sheet1 ).Range( B6 ).Value = Worksheets( Sheet1 ).Range( B7 ).Value = Call the Amazon.com serviceWith oXML.Open GET , strURL, False Needed to web service will recognize get/post.setRequestHeader Content-Type , application/x-www-form-urlencoded .sendEnd With get the resultsstrResponse = oXML.responseText load the results into a new XML documentSet oDom = CreateObject( MSXML.DOMDocument ) oDom.loadXML (strResponse) display the results on the spreadsheetIf oDom.hasChildNodes Then195Calling

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

must install a free SOAPWeb services toolkit so that Microsoft Office can generate the SOAPand otherfiles for you. You also need to download the latest version of MSXML, Microsoft s XMLparser. If youjust want to call a Web API using HTTP/GET (REST) or HTTP/POST, then you install MSXMLif it is notalready installed. You learned in Chapter 1 that the party offering the Web API specifies which of theseprotocols the Web API will support. Let s look at an example of how each of these works. Calling Web APIs Using VBA with HTTP/POSTandHTTP/GET (REST) You do not need to install the Web Services Toolkit for Office if you are not calling a Web API usingSOAP. You can use VBAcode to communicate using HTTPPOST and/or GET (REST). Let s look at abasic example of how this works from Microsoft Excel. Example Calling Amazon.com from Excel Using VBA and HTTP/GET (REST) In this example, you call the Amazon.com API discussed in Chapter 4 from Microsoft Excel. Open Exceland create a new workbook called VBATestFromExcel. On Sheet1 of the workbook, arrange the fields asshown in Figure 9-1. It is important that the cells be laid out exactly as shown in the figure if you wantthe results to be displayed in the correct cells. Use the Visual Basic toolbar to add a button in cell B2 asshown. To view the Visual Basic toolbar, select View.Toolbars.Visual Basic. Figure 9-1194Chapter

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Calling Web APIs fromMicrosoft OfficeIn Chapter 8, you explored various ways that you can call XMLWeb APIs from mobile devices. Youcan also use XMLWeb APIs to expand the features offered in your solutions written in MicrosoftOffice. This chapter covers various ways you can integrate Web APIs into Microsoft Office programs. Some of these methods require an additional software program or toolkit, whereas others can bedone with just Microsoft Office and VBA. More specifically, this chapter covers the following: .Calling a Web API using VBAand HTTPfrom Microsoft Excel .Installing the SOAPWeb services toolkit for VBA .Calling a Web API using VBAand SOAPfrom Microsoft Excel .Installing Visual Studio Tools for the Microsoft Office System .Calling a Web API using the Visual Studio Tools program and SOAPfrom Microsoft Word Calling Web APIs from VBA CodeVBAis an acronym that stands for Visual Basic for Applications. VBAis a programming languagethat is included as part of several Microsoft products, including Access, Word, and Excel. Forexample, Access 2003 VBAuses VBAversion 6.0, which is the same version of VBAused by Word2003 VBAand Excel 2003 VBA. VBAis a programming language that can be used to incorporateadditional features into your applications. VBAshould not be confused with the Microsoft VisualBasic or Visual Basic .NET programming products. VB and VB.NET each has its own syntax of theVisual Basic programming language. The VB syntax, the VB.NET syntax, and the VBAsyntax arevery similar, but there are also differences between them. You can use VBAto call a Web API from a Microsoft Office program. Depending on the type ofWeb service you want to call, you may need to download one or more additional toolkits in orderfor your call to work. For example, if you want to call a Web service from VBAusing SOAP, you12_

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Mac Web Hosting services

Figure 8-10SummaryIn this chapter, you learned how easy it can be to create applications for mobile devices that use XMLWeb APIs. You learned about some of the development tools available, depending on the type of deviceon which you want the application to run. Today s mobile lifestyle demands that developers spend moretime writing applications for devices. The capability to communicate with external Web services usingprograms such as Microsoft Word or Microsoft Excel is increasingly important. Chapter 9 focuses oncalling Web APIs from Microsoft Office. 192Chapter

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Mac Web Hosting services

strResponse = sr.ReadToEnd For simplicity, display XML response on Form Would use XML parser to work with results in desired fashion. txtResults.Text = strResponseEnd Sub8.Save and run the program. You are prompted to specify whether to use an emulator or whetherto test on an actual Pocket PC device. 9.Select Pocket PC 2002 Emulator and click Deploy. As before, Visual Studio .NET takes a minuteor two to set up the emulator environment. Ascreen that emulates a Pocket PC is then displayed, and shortly thereafter the program opens on the emulated screen. The emulated screen containingyour program should be similar to the one shown in Figure 8-9. Figure 8-910.Type search criteria in the designated text box on the device. Then click the Search Amazon.You should see an XMLdocument containing the results of the search, such as thatshown in Figure 8-10.191Calling

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

3.The Smart Device Application Wizard appears. Select Pocket PC and Windows Application andclick OK. 4.Anew Pocket PC project is created, again with the default form sized according to the standardsize of the device. 5.Use the toolbox to drag and drop one label, two text boxes, and a button control onto the Form. 6.Using the Properties window, change the Name property of the first text box to txtCriteria, the Name of the second text box to txtResults, and the Name property of the button tobtnRetrieve. Modify the MultiLineproperty of txtResultsto True. Rename the Textproperty of the labels as shown in Figure 8-8. Figure 8-87.Add the following code to btnRetrieve_Clickof Form1, modifying the code to replace [YourSubscriptionIDHere]with your Subscription ID for the Amazon.COM API: Private Sub btnRetrieve_Click(ByVal sender As System.Object, ByVal e As _ System.EventArgs) Handles btnRetrieve.ClickDim oReq As System.Net.HttpWebRequestDim oResp As System.Net.HttpWebResponseoReq = System.Net.HttpWebRequest.Create( http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&SubscriptionId=[Your Subscription IdHere]&Operation=ItemSearch&SearchIndex=Books&Keywords= & txtCriteria.Text) use the StreamReader to get the responseoResp = oReq.GetResponseDim sr As New System.IO.StreamReader(oResp.GetResponseStream) Dim strResponse As String read the entire stream to retrieve the results190Chapter

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

15.Type in a starting address and an ending address in the text boxes on the device. Then click Directions button. You should see driving directions based on the address you entered, those shown in Figure 8-7. Figure 8-7Example 2 Call Amazon.com API Using HTTP/GET (REST) ProtocolIn this example, you call the Amazon.com API to look up a list of books that match the specified criteria. The Amazon.com API was covered in greater detail in Chapter 4. You will use the HTTP/GET (REST) tocol in this example. You already learned that REST uses a series of parameters included in the URLtocommunicate with the Web API. You also learned that REST returns the results in an XMLdocument. sake of simplicity, our program displays the XMLdocument returned as a result of the call. In your you can use an XMLparser to parse the results and work with them in any way you wish. Let s get started with creating the mobile devices example to use the Amazon.com API. 1.Open Visual Studio .NET and select File.New.Project. 2.Select Visual Basic Project as the Project Type, and select Smart Device Application as the Template. For the project name, specify PocketPCTest2; for the path, specify the location where you wantthe project to be created. Click OK to create the new project.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Mac Web Hosting services

13.Save and run the program. As shown in Figure 8-5, you are prompted to specify whether to usean emulator or whether to test on an actual Pocket PC device. Figure 8-514.If you want to deploy the application directly to the Pocket PC device, you select Pocket PCDevice. For this example, select Pocket PC 2002 Emulator and click Deploy. Visual Studio .NETtakes a minute or two to set up the emulator environment. Ascreen that emulates a Pocket PC isdisplayed, and shortly thereafter the program opens on the emulated screen. The emulatedscreen that appears is similar to the one shown in Figure 8-6. Figure 8-6188Chapter

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Mac Web Hosting services

findService.PreAuthenticate = TrueDim findSpec As New FindSpecificationfindSpec.DataSourceName = MapPoint.NA specify and obtain details for the starting addressfindSpec.InputPlace = txtStartingAddress.TextDim StartingAddress As FindResults = findService.Find(findSpec) specify and obtain details for the ending addressfindSpec.InputPlace = txtEndingAddress.TextDim EndingAddress As FindResults = findService.Find(findSpec) specify the route segment detailsDim rteSegmentSpec(1) As SegmentSpecificationrteSegmentSpec(0) = New SegmentSpecificationrteSegmentSpec(0).Waypoint = New WaypointrteSegmentSpec(0).Waypoint.Name = _ StartingAddress.Results(0).FoundLocation.Entity.NamerteSegmentSpec(0).Waypoint.Location = _ StartingAddress.Results(0).FoundLocationrteSegmentSpec(1) = New SegmentSpecificationrteSegmentSpec(1).Waypoint = New WaypointrteSegmentSpec(1).Waypoint.Name = _ EndingAddress.Results(0).FoundLocation.Entity.NamerteSegmentSpec(1).Waypoint.Location = _ EndingAddress.Results(0).FoundLocation specify the route specification detailsDim routeSpec As New RouteSpecificationrouteSpec.DataSourceName = MapPoint.NA routeSpec.Segments = rteSegmentSpec Specify the credentials for the route service Use the customer id and password you were providedDim routeService As New RouteServiceSoaprouteService.Credentials = New _ System.Net.NetworkCredential( YOUR CUSTOMER ID , YOUR PASSWORD ) routeService.PreAuthenticate = True call the calculate route method to retrieve the routeDim Route As RouteRoute = routeService.CalculateRoute(routeSpec) Dim intCount As IntegerDim strResults As String loop through the results returnedFor intCount = 0 To Route.Itinerary.Segments(0).Directions.Length – 1strResults = strResults & _ Route.Itinerary.Segments(0).Directions(intCount).Instruction & vbCrLfNext intCount display the resultstxtResults.Text = strResultsEnd Sub187Calling

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Mac Web Hosting services

7.Add a reference to the MapPoint API by selecting Project.Add Web Reference. For the URLfield, specify the WSDLfile location, such as http://staging.mappoint.net/standard-30/ mappoint.wsdl. 8.Click the GO button so that Visual Studio locates the MapPoint Web API. 9.Change the Web Reference Name to MapPointso that you can use a shorter name in your pro- ject. At this point, the screen should appear similar to the one shown in Figure 8-4. Figure 8-410.Click the AddReferencebutton to add the reference to your project. Visual Studio .NET usesthe WSDLfile to identify the methods that are available for execution from your project. 11.Add the following imports statement to the top of the code segment (before the PublicClassForm1statement) so that you can refer to the MapPoint service in the project in shorthandmode. Imports PocketPCTest.MapPoint12.Add the following btnRetrieve_Clickevent to the form, inserting your customer ID andpassword in two places in the applicable lines of code: Private Sub btnRetrieve_Click(ByVal sender As System.Object, ByVal e As _ System.EventArgs) Handles btnRetrieve.Click Purpose: Obtain Driving Directions from one address to another Specify the credentials for the find service Use the customer id and password you were providedDim findService As New FindServiceSoapfindService.Credentials = New _ System.Net.NetworkCredential( YOUR CUSTOMER ID , YOUR PASSWORD ) 186Chapter

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Mac Web Hosting services