My Excel file with 1 QueryTable and 3 PivotTables is 9MB in size when refreshed with no data

Question

So here is my problem:

I created an Excel report that will be downloaded from my website, but refreshable on its own. Basically, the user will download the file, then enter their date range on the first tab, and hit a refresh button to grab their data. This data is then retrieved with a stored procedure call and placed in a QueryTable, which 3 separate PivotTables reference.

After developing and testing, I noticed that the filesize was 14MB, but then realized I had saved it with 18,000+ records in the QueryTable. So I figured I would simply refresh the data with dates in the future, thus returning no data, save it, and host that much smaller file that had no data saved.

However, when I saved it this time, even with 0 records in the QueryTable, the filesize is still 9MB. I don't want to host a 9MB file with no data, and I'm sure my users don't want to download a 9MB file with no data.

When I made the QueryTable, I had initally set the SaveData property to True. However, when trying to shrink the filesize, I set it to false every time the data is refreshed. Here is the entierty of my refreshing VBA code:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub RefreshTables()

Dim connstring As String
Dim startDate As String
Dim endDate As String

connstring = "my connection string"
startDate = Range("Parameters!B6").Value
endDate = Range("Parameters!B7").Value

Range("Parameters!D9").Value = "Refreshing..."

Worksheets("Data").ListObjects("DataTable").QueryTable.Connection = connstring
Worksheets("Data").ListObjects("DataTable").QueryTable.CommandText = "EXEC my_storedproc '" & startDate & "', '" & endDate & "'"
Worksheets("Data").ListObjects("DataTable").QueryTable.SaveData = False
Worksheets("Data").ListObjects("DataTable").QueryTable.Refresh BackgroundQuery:=False

Dim w As Worksheet, p As PivotTable
For Each w In ThisWorkbook.Worksheets
    For Each p In w.PivotTables
        p.RefreshTable
        p.Update
    Next
Next

Range("Parameters!D9").Value = "Complete"
Sleep (500)
Range("Parameters!D9").Value = ""

End Sub

Also, I went into each one of my PivotTables to Options > Data and unchecked the "Save source data with file" check box. This did nothing to reduce the file size when saving it.

I feel like Excel is still saving old data in some sort of cache or something, and I can't figure out how to remove it.

paulmorriss paulmorriss · Accepted Answer · 2011-03-16T14:08:07

Could you set up a new spreadsheet with the code and data sources, make the same settings as you say above, and then save it. It's worth refreshing to see if it works, but don't save when you've done that. The file should then be small hopefully.

My Excel file with 1 QueryTable and 3 PivotTables is 9MB in size when refreshed with no data

1 Answers