Organize With Office 365
In Chapter 8, you explored various ways that you can call XML Web APIs from mobile devices. You can also use XML Web APIs to expand the features offered in your solutions written in Microsoft Office. 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 be done with just Microsoft Office and VBA. More specifically, this chapter covers the following Installing Visual Studio Tools for the Microsoft Office System
Most Microsoft Office users will need to use the VBA programming techniques found in Chapter 7 to access eBay Web Services. However, Office 2003 users have an alternative solution that works in some cases. Microsoft is creating a new add-on for the Research Task Pane that should be available when you read this. This add-on lets you perform research on eBay without ever leaving the Office environment. You can find the add-on at http www .office.microsoft.com marketplace. Microsoft Office 2003 Add-on 335 Creating Your Own Research Service for the Microsoft Office 2003 Research Library Customizing the Microsoft Office 2003 Research Task Pane http msdn
Another way you can call Web APIs from Microsoft Office is by calling a .NET program from Office. To call a .NET program from Office, you need Visual Studio .NET 2003, as well as the Visual Studio Tools for the Microsoft Office System program. Both of these are available for purchase from Microsoft, and you can find more information on Microsoft's Web site. Visual Studio Tools for Microsoft Office basically allows you to use the Visual Studio .NET interface to create code that gets called from Microsoft Office documents. You can use the same code and familiar interface as you do with Visual Studio .NET, and you can also run those procedures from a particular document or spreadsheet. This means that you can call a Web API using SOAP or HTTP with nearly the same code as described in the earlier chapters.
The example won't work unless you configure it for use. Actually, you need to perform this configuration for any Visual Studio .NET application written for Microsoft Office. The .NET Framework applications require this configuration as a security precaution to ensure add-ins have your permission to run. The process is relatively simple, but you must have administrative privileges. The following steps show you how to perform this task.
Now, let's walk through a simple example that uses the Visual Studio Tools for Microsoft Office to call the Amazon.com Web service from Word. In this example, you write code to look up all books that are similar to a particular book. Those similar books are then displayed in a Microsoft Word document. Expand the Project Types nodes so you can see all the Microsoft Office System Projects. Select Visual Basic Projects, and then select Word Document for the Template. Click OK, and a screen similar to Figure 9-14 appears. Microsoft Office Project Wizard - WordExample
This chapter has demonstrated how you can use VBA to write programs that use eBay Web Services. It's essential to remember that the techniques in this chapter work with any application that supports VBA, not just Microsoft Office. The capabilities of the application also affect how you interact with eBay Web Services. Yes, you can force a spreadsheet to act as a
Many other tools are available, provided by companies other than eBay, and are designed to help you manage your eBay business in many different ways. We recently found 85 different tools. Some tools attempt to do everything, while others specialize in a certain area, such as inventory management. Some integrate with QuickBooks, while some are adapted for Great Plains or Microsoft Office.
EBay Web Services has been so successful that many language vendors are beginning to take notice, as well as a number of third parties. The following list presents a few of the most interesting places to find eBay Web Services information. However, you should also check with the vendor that creates your programming language product and look around at other third party solutions too. For example, the Microsoft Office 2003 Add-on section describes how Microsoft is adding Web services support to their Office product.
To install Visual Studio .NET, launch the setup program and follow the onscreen prompts to complete the setup process. To install the Microsoft Visual Studio Tools for the Microsoft Office System program, launch the setup program. A screen similar to the one shown in Figure 9-10 is displayed.
Many of the tools mentioned in this book rely on Microsoft XML Core Services (MSXML) 4.0. In addition, some of the coding examples also rely on this library. The latest version at the time of writing is Service Pack (SP) 2, which you can download at http www.microsoft.com (MSXML 4.0 is approximately 5MB, so make sure you allocate enough time to download it). Both of the editors in the sections that follow rely on MSXML 4.0. However, Netpadd is perfectly happy using MSXML 5.0, which comes with Microsoft Office 2003. On the other hand, XMLwriter 2 specifically requests MSXML 4.0 every time you start it, even if you have MSXML 5.0 installed. Fortunately, you can install the two versions of MSXML side by side without any ill effects.
This chapter won't teach you how to use VBA. I'm assuming that you already know enough about VBA to create your own simple applications. If you don't already have this knowledge, you can get it from a number of sources. The first place to look is my book, VBA for Dummies, Fourth Edition (Wiley, 2003). This book introduces you to VBA and takes you through examples using all of the major Microsoft Office applications. You'll also want to look at some of the resources that Microsoft provides. For example, you'll find the various Microsoft Office Resource Kits at http www.microsoft.com office ork home.htm, or Make sure you check out the Office Developer's Center at http msdn.microsoft.com office . The Microsoft Office site includes some interesting tools at http www.microsoft.com office ork 2000 appndx toolbox.htm. Office 2003 users can find similar information on their product at
Many people associate Visual Basic for Applications (VBA) with Microsoft Office. While it's true that Microsoft Office does rely on VBA as a development language, you'll find VBA used with other applications too (see the list at Therefore, even though the examples in this chapter do rely on Microsoft products, you can use the information with any product that supports VBA.
You can use VBA to call a Web API from a Microsoft Office program. Depending on the type of Web service you want to call, you may need to download one or more additional toolkits in order for your call to work. For example, if you want to call a Web service from VBA using SOAP, you must install a free SOAP Web services toolkit so that Microsoft Office can generate the SOAP and other files for you. You also need to download the latest version of MSXML, Microsoft's XML parser. If you just want to call a Web API using HTTP GET (REST) or HTTP POST, then you install MSXML if it is not already installed. You learned in Chapter 1 that the party offering the Web API specifies which of these protocols the Web API will support. Let's look at an example of how each of these works. As you can see, the first book matching the criteria specified is displayed in the cells on Sheet1. This is just a simple example of how you can use VBA with Microsoft Office to call Web APIs using HTTP.
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. Download details Microsoft Office 2003 Web Services Toolkit 2.01 - Microsoft Internet Explorer - n 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...
At this point, you have explored various Web APIs and how to call those APIs from various environments, including Microsoft Office and mobile devices. These final two chapters provide two case studies that illustrate how to use multiple APIs together in your own custom solution. This chapter focuses on a Customer Relations Management (CRM) Application that uses the Google API and the Microsoft MapPoint API. More specifically, the chapter will cover
In this chapter, you looked at the popular Google search engine and how to make use of the Google API in your own programs. You now have enough information to be dangerous in using Google in your own applications. For more information about using the Google API, please consult the later chapters of this book that illustrate using Web APIs from Microsoft Office, from mobile devices, and in the comprehensive case studies.