Writing Excel Macros with VBA, 2nd Edition

Chapter 20. Pivot Tables

In this chapter, we take a look at pivot tables and how to create and format them using code.

Pivot Tables

While we are assuming that the reader is familiar with the basics of Excel, it probably would not hurt to review the concept of a pivot table (or PivotTable) quickly.

PivotTables are one of the most powerful features in Excel. They are designed to accomplish three main tasks:

• Import external data

• Aggregate data; for example, sum, count, or average the data

• Display the data in interesting ways

PivotTables can use data from external sources, as well as from one or more Excel tables. For instance, the data for a PivotTable can come from an Access database. However, setting up Excel to import external data requires that the appropriate data source drivers be installed on the user’s computer. Moreover, there are significant limitations on Excel’s ability to import data through PivotTables. For instance, all strings are limited to a length of 255 characters, which makes using SQL to define a data source much more difficult.

All in all, importing data using a PivotTable can be problematic. Furthermore, we always have the option of importing the required data directly to an Excel worksheet (using a variety of more sophisticated methods, such as DAO and the GetRows method) and then creating the PivotTable from the worksheet. Accordingly, we will restrict our discussion to using Excel data as the PivotTable source.

Table 20-1, which represents sales ...