XLNS

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