This is from the following thread to give you the background.
It is an example of how you can use the power of SQL within XL to manipulate & return data from linked tables (as in a relational database) to be used in a Pivot Table.
The code in the file was developed from Dennis' posts in the Advanced Integration forum so full credit to hime for the inspiration
To explain, for those who can't download the file.
Step 1. Set up the Raw data in Two Tables
I created a table of data in a sheet named TblMajor (sheet name - no need for named range) with fields as follows:
I created a second table in another sheet - the sheet called tblMinor
Normally, to get the revenue number of Sales * Price, this is where you would need VLOOKUP or INDEX/MATCH - but this is also where the power of ADO & SQL comes to the fore....
Step 2. Set up the SQL Query
I created a separate worksheet called PivotSource, with the following header row in row 1
I then added the following code to a standard worksheet module
'Credit to XL-Dennis for the original code ideas.
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String, stSQL As String
Dim i As Long, lnMode As Long
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("PivotSource")
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & wbBook.FullName & ";" _
& "Extended Properties=""Excel 8.0;HDR=YES"";"
stSQL = "SELECT [TblMajor$].CustID, [TblMajor$].ProdID, [TblMajor$].Period, [TblMajor$].SalesVolume,"
stSQL = stSQL & " [TblMinor$].ProdPrice,[TblMinor$].ProdPrice * [TblMajor$].SalesVolume "
stSQL = stSQL & " FROM [TblMajor$], [TblMinor$]"
stSQL = stSQL & " WHERE [TblMajor$].ProdID = [TblMinor$].ProdID"
stSQL = stSQL & " ORDER BY [TblMajor$].CustID ASC"
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
rst.Open stSQL, cnt, adOpenStatic, adLockOptimistic
'A check to see that records actually exist.
If Not rst.EOF Then
.ScreenUpdating = False
'Collect the present calculation-mode.
lnMode = .Calculation
.Calculation = xlCalculationManual
'Copy the records to the PT Data worksheet.
wsSheet.Cells(2, 1).CopyFromRecordset rst
'Reset the calculation-mode.
.Calculation = lnMode
.ScreenUpdating = True
MsgBox "No records could be find!", vbCritical
If CBool(rst.State And adStateOpen) Then rst.Close
Set rst = Nothing
If CBool(cnt.State And adStateOpen) Then cnt.Close
Set cnt = Nothing
The SQL has the effect of multiplying the Sales by the Product Price, just as you would have done with VLOOKUP... but no memory issues & no overheads.
You need to set a reference in the VBE to MDAC 2.5 or greater to get the ADO to work.
Step 3. Build your Pivot Table
So now we have the raw data with all the fields/info we want from the 2 tables. I then added a named range to the PivotSource sheet called PTSource & the refers to box contains the following
Making the range fully Dynamic
Step 4. Build your Pivot Table
Finally I added a sheet called PivotTable, which is where I built the PT
I merely added the following code to ensure that ensured that the source data is updated each time PT is refreshed
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call Create_Pivot_Source ' refresh source data via ADO/SQL
Application.EnableEvents = False 'stop indefinite looping
.RefreshTable ' refresh the PT
Application.EnableEvents = True 'turn back on after refresh event
Hope that this helps.
Here is the example file for those that might be interested. I will also post this in Advanced forum.
(Edit - please read the following KB article