Home > Excel, Pivot table > Update Pivottable through swapping the pivotcache

Update Pivottable through swapping the pivotcache

May 18th, 2011 admin

March 25th, 2009

First of all, let me start by appologizing for not blogging for xxx weeks.
Having done that I want to blog about the use of pivottables for reporting to management.
Recently I started working at a new company and discovered a giant heap of workbooks.
A lot of them are used for reporting to managent. For example there is one workbook which
contains 10 pivottables based on basically two recordsets. The size of this monster is
almost 100 MB because each pivottable has its own pivotcache.

The same workbook is refreshed every day and saved with new data on the network with
a new name. This means 2G of storage space every month. The refreshing is done by
changing a cell with the dateparameter in it and updating the pivotcaches through a macro
called by a button on the sheet.

What I dislike about this aproach is the button on the sheet, the size of the workbook(s)
and the dependancy on odbc links. So I started thinking on a new setup for this workbook.
The first abvious thing was changing to 2 main pivotcaches this would reduce the size to less
than 8 MB whitch means a 90% reduction of disk space.

Secondly I wanted to refresh the data by connecting a new pivotcache to the pivottables.
In the past I wrote some code to switch pivotcaches between pivottables and to produce a
pivotcache from a sql statement and a connectionstring. It looked liked I was almost there
but there were a couple of pitfalls. Apperently you must create a pivottable first from a
pivotcache to be able to switch the pivotcache. Also when the your pivottable uses calculated
fields in the value section switching the cache results in a blank pivottable.

After realizing these things the coding didn’t take along time. Altough everything works I would like a answer from microsoft or anybody else about the problem around using a pivotcache without pivottable and the calculated field issue. Below the code without error handling for the obvious reasons. Comments a very welcome.

Sub UpdateWorkbook()

   Dim sSQL1 As String: Dim sSQL2 As String: Dim sDate As String

   sDate = InputBox(”Enter the date (yyyymmdd)”)

   sSQL1 = “SELECT *, (new-old) AS change FROM table1 WHERE date=’” & sDate & “‘”
   sSQL2 = “SELECT * FROM table1 WHERE date = ‘” & sDate & “‘”

   SwapPivotCaches sSQLServer, sSQL1, “PivotTable1″
   SwapPivotCaches sSQLServer, sSQL2, “PivotTable2″

End Sub
Sub SwapPivotCaches(sConnection As String, sSQL As String, sPivotTable As String)

   Dim pcTemp As PivotCache: Dim ptTemp As PivotTable
   Dim ws As Worksheet: Dim pt As PivotTable

   Application.ScreenUpdating = False

   Sheet0.Visible = xlSheetVisible

   Set pcTemp = PivotCacheFromSQL(sSQL, sConnection)
   Set ptTemp = pcTemp.CreatePivotTable(TableDestination:=Sheet0.Range(”B5″))

   For Each ws In ActiveWorkbook.Worksheets
      For Each pt In ws.PivotTables
         If pt.Name = sPivotTable Then pt.CacheIndex = ptTemp.CacheIndex
      Next pt
   Next ws

   Set ptTemp = Nothing
   Set pcTemp = Nothing

   Sheet0.Cells.Delete
   Sheet0.Visible = xlSheetVeryHidden
   Application.ScreenUpdating = False

End Sub
Function PivotCacheFromSQL(sSQL As String, sConnection As String) As PivotCache

   Dim cConnection As ADODB.Connection
   Dim rsRecordset As ADODB.Recordset
   Dim cmdCommand As ADODB.Command
   Dim pcPivotCache As PivotCache
   Dim iErrorCounter As Integer

   iErrorCounter = 0

   Set cConnection = New ADODB.Connection
   cConnection.Open sConnection

   Set cmdCommand = New ADODB.Command
   Set cmdCommand.ActiveConnection = cConnection

   With cmdCommand
      .CommandText = sSQL
      .CommandType = adCmdText
   End With

   Set rsRecordset = New ADODB.Recordset
   Set rsRecordset.ActiveConnection = cConnection

   rsRecordset.Open cmdCommand

   Set pcPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
   Set pcPivotCache.Recordset = rsRecordset

   Application.StatusBar = False

   Set PivotCacheFromSQL = pcPivotCache
   Set rsRecordset = Nothing
   Set cmdCommand = Nothing
   Set cConnection = Nothing

End Function
Categories: Excel, Pivot table Tags: ,
Comments are closed.