Chapter 25: Excel and the Web

---

Overview

Back in the day, a typical Microsoft Excel-based application was almost entirely contained within Excel itself; the only external interaction would be with the user. The user would provide the data and then present the results. If you needed to store the data, you would use separate worksheets and try to simulate a relational database as best you could.

As data access technologies developed from Open Database Connectivity (ODBC) drivers, through Data Access Objects (DAO) to the current versions of ActiveX Data Objects (ADO), it became more commonplace to store data in external databases and even retrieve data from and update data in other systems across the network. It's now quite common to see Excel used as a front-end querying and analysis tool for large corporate databases, using QueryTables and PivotTables to retrieve the data. The data available to your Excel applications was limited to data available on the company network, and to those databases that you could get permission to access.

Starting with the release of Microsoft Office 97, Microsoft has slowly extended Excel's reach to include the Internet and associated technologies. Excel has added Web functionality directly into Excel, such as Web Queries, as well as ensuring that Excel developers can easily use standard external objects, such as the Internet Transfer Control, the Web Browser control, and the MSXML parser. These objects are included within the Office installation.

Excel provides you with sufficient functionality to reconsider your approach to developing Excel applications. You can start to think outside of the pure Excel/ADO environment in terms of obtaining data, publishing results, monitoring your applications, and sharing data outside of the corporate network.

This chapter introduces the functionality available to you in Excel and demonstrates how to interact with the Internet in your applications. You'll learn how to save a worksheet as a Web page, publish a worksheet to the Web, make the worksheet interactive, and update the worksheet programmatically. Once you can save the worksheet as a Web page, you'll learn how to use existing Web pages as a data source. You'll start with opening a Web page in Excel, create and use Web Queries, and parse Web pages for specific information. To finish off the chapter, you'll evaluate how to set up and communicate with a Web server.

---