| Microsoft Office 2000/Visual Basic Programmer's Guide | |
To create a PivotTable report from an external data source, such as an Access or SQL Server database, you specify xlExternal for the SourceType argument of the PivotTableWizard method, and a single-element, two-dimensional array for the SourceData argument. The first element of the array should contain the connection string for the data source, and the second element should contain the SQL statement to retrieve the data.
The connection string must be a valid Open Database Connectivity (ODBC) connection string. For example, the following constant defines an ODBC connection string for the Northwind sample database that is installed with Access:
Public Const ODBC_CONNECT As String = "ODBC;" _
& "DBQ=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;" _
& "Driver={Microsoft Access Driver (*.mdb)};"
If your data is coming from a relational data source that is properly normalized, you'll almost certainly have to define a query with multiple joined tables in order to get the data you want in the PivotTable report. By definition, a PivotTable report displays data from more than one table. For example, suppose you're creating a PivotTable report from the Northwind sample database to display information about sales by customer, customer city or customer country, and product or category. You'll need to create a query that includes the Customers, Products, Categories, and Order Details tables, at a minimum.
As noted earlier, lookup fields in an Access database can be problematic when you move data to Excel, because Excel displays only the stored numeric value, not the text value that appears in Access. In order to retrieve the text value, you must define a query containing inner joins between tables. If your query includes more than two tables, the SQL statement for such a query quickly becomes complex.
If the SQL statement has 255 or fewer characters, you can use it as-is as the second element of the two-dimensional array. If it's a longer string, however, you must either parse the string into 255-character segments, or create a saved query in the data source, if it supports saved queries, and base the SQL statement included in the array on that query.
If you choose to parse the SQL string into 255-character segments, you can simply add each segment as an additional element in the array, as long as the first element is the connection string. When you pass the array as the SourceData argument, Excel concatenates the strings together to form the complete SQL string. For example, the following line of code creates the array for the SourceData argument from four strings that include contiguous segments of the SQL statement:
varSource = Array(strConnect, strSQL1, strSQL2, strSQL3, strSQL4)
Parsing the SQL string into 255-character segments can be a hassle. If your data is in an Access database and you build the SQL statement in the Access query design grid, it's easiest to create a saved query in the database and use a simple SQL statement to retrieve data from that query. For example, the following code fragment creates a saved query based on a lengthy SQL string in an Access database. It then creates the two-dimensional array containing the connection string and a second SQL string that retrieves records from the saved query.
' Create new query in Northwind and append to Views collection.
' This is one way to handle the problem of passing an SQL string
' longer than 255 characters.
' Open new ADO connection.
Set cnnNwind = New ADODB.Connection
cnnNwind.Open ADO_CONNECT_STRING
Set catDb = New ADOX.Catalog
' Open catalog on data source.
Set catDb.ActiveConnection = cnnNwind
' Create new command.
Set cmdQuery = New ADODB.Command
' Specify SQL string as command text.
cmdQuery.CommandText = REGIONAL_SALES_SQL
' Before appending new view, delete query with the
' same name, if it exists.
On Error Resume Next
catDb.Views.Delete QUERY_NAME
On Error GoTo 0
' Append new view.
catDb.Views.Append QUERY_NAME, cmdQuery
' Create array containing connection string and SQL string.
varSource = Array(ODBC_CONNECT_STRING, "SELECT * FROM " & QUERY_NAME)
' Create PivotTable report. Start at cell B8 so there is enough room
' for page fields.
Set pvtTable = wksPivot.PivotTableWizard(xlExternal, varSource, _
wksPivot.Range("B8"))
This code fragment is taken from the CreatePivotTableFromMDB procedure in the modPivotTable module in the Northwind.xls sample file, which is available in the ODETools\V9\Sampes\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM.