In the preceding example, the source data was in a worksheet. As you probably know, Excel also enables you to use an external data source to create a pivot table. The example in this section demonstrates how to write VBA code to create a pivot table based on data stored in an Access database file.
The Access database consists of a single table that is identical to the data used in the previous example.
The code that creates the pivot table is shown in Listing 17-3. It assumes that the budget.mdb database file is stored in the same directory as the workbook.
Listing 17-3: Generating a Pivot Table from an External Database
Sub CreatePivotTableFromDB() Dim PTCache As PivotCache Dim PT As PivotTable
Listing 17-3 (Continued)
' Delete PivotSheet if it exists On Error Resume Next Application.DisplayAlerts = False Sheets("PivotSheet").Delete On Error GoTo 0
' Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add _ (SourceType:=xlExternal)
' Connect to database, and do query
DBFile = ThisWorkbook.Path & "\budget.mdb"
ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile
QueryString = "SELECT * FROM BUDGET" With PTCache
.Connection = ConString .CommandText = QueryString End With
' Add new worksheet Worksheets.Add
ActiveSheet.Name = "PivotSheet"
' Create pivot table
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1"), _ TableName:="BudgetPivot")
' Add fields
With PT ' Add fields
.PivotFields("DEPARTMENT").Orientation = xlRowField .PivotFields("MONTH").Orientation = xlColumnField .PivotFields("DIVISION").Orientation = xlPageField .PivotFields("BUDGET").Orientation = xlDataField .PivotFields("ACTUAL").Orientation = xlDataField End With End Sub
Notice that the SourceType argument for the Add method of the PivotCaches collection is specified as xlExternal. In the example in the previous section (which used data in a worksheet database), the SourceType argument was xlDatabase.
The PivotCache object needs the following information to retrieve the data from the external file:
♦ A connection string: This describes the type of data source and the filename. In this example, the connection string specifies an Open Database Connectivity (ODBC) data source that is a Microsoft Access file named budget.mdb.
♦ A query string: This is a Structured Query Language (SQL) statement that determines which records and fields are returned. In this example, the entire Budget table is selected.
This information is passed to the PivotCache object by setting the Connection and CommandText properties. After the data is stored in the pivot cache, the pivot table is created by using the CreatePivotTable method.
SQL is a standard language for performing database queries. For more information, consult the online help. Better yet, you might want to purchase a book that deals exclusively with SQL.
Was this article helpful?