Programming Excel with VBA and .NET

Connect to an External Data Source

Pivot tables based on worksheet data are handy, but it is probably more common to use pivot tables with external data sources such as databases. To get pivot data from a database:

1. Create a new worksheet and choose Data → Pivot Table and Pivot Chart Report. Excel starts the PivotTable Wizard (Figure 13-2).

2. Select External Data Source, click Next, and then click Get Data. Excel displays the Choose Data Source dialog (Figure 13-12).

   

   Figure 13-11. Use page fields to view individual items

   

   Figure 13-12. Use the Query Wizard to connect to an external data source

3. Follow the Query Wizard steps to connect to the data source. The information required and steps displayed vary based on the type of database. After you connect, the Query Wizard allows you to compose a query to retrieve data from the database as shown in Figure 13-13.

4. Click Next and follow the Query Wizard steps to complete the query. When done, Excel displays Figure 13-14.

5. Choose Return Data to Microsoft Office Excel and click Finish. Excel returns you to the PivotTable Wizard.

6. Click Finish to create the pivot table.

The completed pivot table appears like any other pivot table. You can drag fields from the PivotTable Field List onto the pivot areas, add formatting, and chart the data as you like. Figure ...