Pivot Table caching data even when "Save source data with file" is unchecked

Question

I'm trying to create a pivottable that doesn't save it's data as part of the file. I only want it to retrieve data each time it opens (for security reasons). If it can't access the source data for whatever reason, then I want the pivottable to be blank.

I can get this working with a normal "external data table" within the spreadsheet, by setting it's connection to "Refresh data when opening the file" and "Remove data from the external data range before saving the workbook". When I open the workbook, the external data table is blank until the data connection has been refreshed - exactly what I want.

However, when I create a pivottable using this same "external data table", the data is saved into the spreadsheet, even if I uncheck Pivot Table Options > Data > Save source data with file.

I've also tried creating a separate data connection for the pivottable. When I do this, the "Remove data from the external data range before saving the workbook" option in the connection usage tab is disabled.

How can I prevent Excel from displaying data in the pivottable until it has been refreshed?

Answer 1

Now done:-

http://social.technet.microsoft.com/Forums/en-US/excel/thread/a8711bce-e655-42f0-99d0-bf71f2e5fa76

Answer 2

You may post your query at the forum below:

http://social.technet.microsoft.com/Forums/en/excel/threads