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

Here's my problem:

I created an Excel report that will be downloaded from my website , But refreshing on its own, the user will download the file, then enter your date range for the first time and press a fresh button to receive your data. This data is then retrieved from a stored procedure call and a query is kept in the table, which is the 3 different pivetables reference.

After the development and testing, I found that the file size was 14 MB, but then it felt that I had saved it to 18,000+ records in QueryTable, so I thought I would send data in the future with dates I will refresh, so no data will return, save it, and will host that there was no data left in very small file.

However, when I saved it this time, with 0 records in QueryTable, the files are still 9MB. I do not want to host a 9 MB file with any data, and I'm sure My users want to download 9 MB file with no data.

When I created a query table, I was initally setting the service data to true. However, when trying to minimize filesync, every time when the data is refreshed, I lie it Make me Here is the entierty of my refreshing VBA code: sub-sleeping Libel "kernel32" (longer as ByVal dwMilliseconds) Sub-refreshTables () Dim connstring string string startmade as string startmade string conmstring value "Range (" parameter! D9 ") value =" refreshing ... "worksheets (" data "). List objects ("DataBable"). QueryTable.Connection = connstring worksheet ("data"). List objects ("DataBable"). QueryTable.CommandText = "EXEC my_storedproc" & amp; Start & amp; "',' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' '' "Full" sleep (500) range ("parameter! D9"). Value ("parameter! D9"). Next range ("parameter! D9"). Value = "" end sub < / Pre>

In addition to this, I went into each of my PivotTables options & gt; Data and "Save Source Data with File" check box. Uncheck Nothing done to reduce the file size while saving it.

I think Excel is still saving old data in some kind of cache or some things, and I do not know Think how to remove it.

Can you set a new spreadsheet with code and data sources, as you mentioned above They make the same settings, and then save it to see it fresh It works, but it does not save when you have not finished this task. The file should be small hopefully.