Pivot Tables and External Data huge file size swell

[KarlSch]

Hi there

I'm using Excel 2007.

I have the following problem. I have 5 large data sheets (over 400,000 records each) that I want to analyse using various different sheets utilizing pivot tables. I want to update the input sheets periodically, and then just have the pivot tables in the analysis sheets change their data source.

To achieve this I run the following code to update the data source;

Option Explicit

Sub ChangeSource()

Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
NewPath As String

NewPath = [Path]

For Each sh In ActiveWorkbook.Sheets

For Each pt In sh.PivotTables

pt.SourceData = NewPath

Next pt

Next sh

MsgBox ("Done")

End Sub


Problem is, it seems that each and every pivot table now cashes the data source, meaning that my analysis sheet is growing to 100mb+ and increases by about 8mb with each pivot table I add.

Is there any way I can fix this?

Thanks
Karl

 

[c_m_s_jr]

I believe there is an option with Pivot tables to tell it not to save the cache with the table, but any individuals you want to send this to will have to refresh the pivot tables to make sure the data is correct.

I know in 2003 you would uncheck the "Save Data with table layout" under "Options". I don't have 2007 up in front of me so I can't say for sure where that is. Also I am no VBA wizard so not sure what the code would be.

On a side note, if you are dealing with over 400,000 records on 5 sheets I would not be doing your analysis in Excel. I would take it in MS Access or try and get summarized data from the original source to cut down the size (that's my two cents...)

 

[KarlSch]

Normally I would agree on not using Excel for such large files, but apart from the swell in size Excel actually handles it quite well.

Anyway, I've managed to fix the problem. I've added an additional line that makes all the pivot tables use the same cache. This brings the size down from 100mb+ to around 8mb.


Karl



Option Explicit

Sub ChangeSource()

Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim ptStore As PivotTable
Dim NewPath As String
Dim i As Integer

NewPath = [Path]

i = 0
For Each sh In ActiveWorkbook.Sheets

For Each pt In sh.PivotTables
pt.SourceData = NewPath

If i = 1 Then
pt.CacheIndex = ptStore.CacheIndex
Else
Set ptStore = pt
i = 1
End If


Next pt

Next sh


MsgBox ("Done")

End Sub