Excel is ultimately limited in the quantity of data it can store, and it is very poor at handling multiple related Tables of data. Therefore, you might want to store your data in an external database application and draw out the data you need as required. A powerful way to do this is to use ADO (ActiveX Data Objects), a topic covered in greater depth in Chapter 20.
The following example shows how to connect to an Access database called SalesDB.accdb containing data similar to that you have been using, but potentially much more comprehensive and complex. To run the following code, you must create a reference to ADO. To do this, go to the VBE window and click Tools O References. From the list, find Microsoft ActiveX Data Objects and click in the checkbox beside it. If you find multiple versions of this library, choose the one with the highest version number.
When you run the code, it creates a PivotCache, creates a new worksheet at the front of the workbook, and adds a PivotTable that is similar to those you have already created, but the data source will be the Access database:
Sub PivotTableDataViaADO() Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String Dim pvc As PivotCache Dim pvt As PivotTable
'Set up connection
Set con = New ADODB.Connection con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=C:\Data\SalesDB.accdb;"
'Define SQL statement sSQL = "Select * From SalesData"
'Open the recordset Set rs = New ADODB.Recordset Set rs.ActiveConnection = con rs.Open sSQL
'Create the PivotTable cache
Set pvc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) Set pvc.Recordset = rs
'Create the PivotTable Worksheets.Add Before:=Sheets(1)
Set pvt = ActiveSheet.PivotTables.Add(PivotCache:=pvc, _ TableDestination:=Range("A1"))
.NullString = "0" .SmallGrid = False
.AddFields RowFields:="State", ColumnFields:="Product" .PivotFields("NumberSold").Orientation = xlDataField End With
First you create a Connection object linking you to the Access database using the Open method of the ADO Connection object. You then define a SQL (Structured Query Language) statement that says you want to select all the data in a Table called SalesData in the Access database. The Table is almost identical to the one you have been using in Excel, having the same fields and data. See Chapter 20 to get more information on SQL and the terminology that is used in ADO.
You then assign a reference to a new ADO Recordset object to the object variable rs. The ActiveConnection property of rs is assigned a reference to the Connection object. The Open method then populates the recordset with the data in the Access SalesData Table, following the instruction in the SQL statement.
You then open a new PivotCache, declaring its data source as external by setting the SourceType parameter to xlExternal, and set its Recordset property equal to the ADO recordset rs. The rest of the code uses techniques you have already seen to create the PivotTable using the PivotCache.
Chapter 20 goes into much more detail about creating recordsets, and with a much greater explanation of the techniques used. Armed with the knowledge in that chapter, and knowing how to connect a recordset to a PivotCache from the previous example, you will be in a position to utilize an enormous range of data sources.
Was this article helpful?