Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 26Managing External Data

One of the most versatile and useful benefits of Excel is its ability to import data from external sources. Lessons 29–33 include examples of sharing data back and forth with other Microsoft Office applications from Excel.

Prior to Excel 97, data in an Excel workbook was entered manually. An Excel workbook was essentially a self-contained object, having almost no contact with the outside world except for the person working in the project.

Starting with Office 97, Microsoft became devoted to providing more and better tools for importing and exporting data to the Internet, database programs, and text-related software applications. Excel leads the way in this effort among all Office applications. In this lesson, you learn how to use VBA to share data between Excel and other external sources, including Access, the Internet, and text files.

Creating QueryTables from Web Queries

The Internet as we know it has only been around since the mid-1990s—not that long ago really—but it's hard to imagine what life would be like today without the World Wide Web. The public's desire is only increasing for access to the galaxy of information that is stored on the web. With each new release of its Office suite, Microsoft has improved the capacity of its applications to interact with web-based information.

NOTE When you connect Excel to an external source such as the Internet, you add a QueryTable to your worksheet. Objects that can connect to external data ...