update pivot table populated by recordset - adodb

[cloudywaw]

Hi!

If I use the procedure shown in this post (copy below, procedure name "createPivotTable") to create a recordset from a worksheet or a named range, and then use the recordset to create and then populate a pivot table, what will the correct procedure be to update the recordset and then to update the pivot table if:

1. values in the existing data source change,
2. if the number of records in the existing data source changes,
3. if the structure of the existing data source changes (add, take away columns/fields)
4. if I need to add a second or third worksheet or named range to the data source?

I tried the procedure below (name "refreshPivotTable") but it does not always work, specifically:

1. if I add an additional worksheet or a named range to the data source, then the update procedure fails (the procedure does not recognise the name of the new data range). I then need to save the file, close Excel, reopen the file and rerun the procedure, at which time the new data range is taken into consideration and the pivot table refreshes correctly,
2. changes to the structure of the data source do not show after the pivot table is refreshed, i.e. new fields do not show in the pivot table, or they do show but the values which belong to this field do not show in the pivot table.

Therefore, is there a better way to make the pivot table update as my source data changes? Am I doing this the wrong way?

Any tip will be greatly appreciated.

Tom



Sub createPivotTable()
With ActiveWorkbook
arSQL = "SELECT * FROM [03-11$]"
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open arSQL, _
Join$(Array("Provider=Microsoft.jet.OLEDB.4.0; Data Source=", _
.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
End With
Set objPivotCache = .PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = objRS
Set objRS = Nothing
objPivotCache.CreatePivotTable TableDestination:=Sheet1.Range("A3")
Set objPivotCache = Nothing
End Sub


Sub refreshPivotTable()
With ActiveWorkbook
arSQL = "SELECT * FROM [03-11$] UNION ALL SELECT * FROM [04-11$]"
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open arSQL, _
Join$(Array("Provider=Microsoft.jet.OLEDB.4.0; Data Source=", _
.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
End With
Set ThisWorkbook.PivotCaches(1).Recordset = objRS
Sheet1.PivotTables(1).PivotCache.Refresh
Set objRS = Nothing
End Sub