Excel® 2007 VBA Programmer's Reference

Chapter 21. Managing External Data

Chapter 20 discussed how to access data with essentially unlimited flexibility using ADO. Excel also provides some built-in data management features, primarily through the QueryTable, ListObject, and WorkbookConnection objects. Excel's built-in data management features have less flexibility than custom ADO programming. For example, you can only use these features to retrieve data, not modify it. But they are simpler and offer a number of useful capabilities right out of the box. This chapter examines some of Excel's built-in data management capabilities.

The External Data User Interface

The built-in data management features in Excel 2007 are accessed from two groups on the Data tab of the Ribbon in the Excel 2007 user interface. The Get External Data and Manage Connections groups are shown in Figure 21-1.

Figure 21-1. Figure 21-1

The controls on the Get External Data group are used to create new connections from your workbook to various data sources, and the controls on the Manage Connections group are used to manage data connections that already exist in your workbook.

Get External Data

The controls in the Get External Data group allow you to retrieve external data directly from various data sources or use predefined queries stored in various data connection files. The three buttons along the left side of the Get External Data group provide quick access ...