In this chapter, we take a look at pivot tables and how to create and format them using code.
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 from a ...