File size of an Excel Workbook increases every time I create a new pivot table and save the file

Question

I have a vba macro that creates a pivot table based on some data in an input sheet. Every time the macro runs, the old pivot is deleted and a new one is created.

The problem I'm facing is that every time I save the file after running the macro, the file size increases by roughly 14MB.

This is how I delete the old pivot table:

For Each pivot In reportSht.PivotTables
    pivot.TableRange2.Clear
Next pivot

My theory is that some part of the pivot isn't being deleted but I can't seem to put my finger on what.

Answer 1

I have found the solution to my problem. When I create the pivot tables I also add connections, since I need to display the number of unique entries in the pivot table:

ActiveWorkbook.Connections.Add2 "WorksheetConnection_" & inputDataArea, "", _
    "WORKSHEET;" & ActiveWorkbook.Path & "\[" & ActiveWorkbook.name & "]" _
    & inputSht.name, inputDataArea, 7, True, False

Where inputDataArea is a String with the range used by for the pivot table. My problem was, that I was not deleting these connections when I was deleting the pivot table. So a new connection was being added every time the macro was executed.

I added this piece of code to also remove any connections that are no longer needed after removing the pivot table:

Dim connection As Object

For Each connection In ActiveWorkbook.Connections
    If connection.name <> "ThisWorkbookDataModel" Then connection.Delete
Next connection

The file is still large but manageable and most importantly it's not growing anymore.

Thanks to Pᴇʜ for suggesting that I remove pivot caches and pointing out, that these are deleted along with the connections.