External Data annoyance when using Pivot Table in template

Question

Repeatable issue in Excel (2007-2010) -

Easily reproduced:

In a new workbook,

• Create a quick grid of data, say, 10x10 cells, fill it with numbers - no need to be fancy
• Select those cells, make a pivot table from them  - again, nothing fancy or even meaningful
• Save the workbook as a template - xltx or xltm, it doesn't matter.

The message:"This workbook contains external data. Do you want....", appears, first time, and every save thereafter.

Now

Save this same error-throwing template as an xlsx or xlsm; the message does not appear.

Are you thinking maybe it was just something funky with the initial save to template?

Nope -

Save that xlsx (or xlsm) back as a template - external data message reappears!

No data has moved, nothing's been added or changed. 

This seems like inappropriate behavior to me.

It does this in Excel2007 on an XP machine, and in Excel2010 on a Win7 machine.

Why should a pivot table not safely exist inside a template without throwing a bogus external data error?

Is there any way to dance around this? I haven't found it yet...

Answer 1

Hi Guys, I just found this information on some other blog:

"It’s not necessarily “External data”; it's actually the Pivot Cache that it wants to clear, so the message is legitimate, but, in true Microsoft fashion, it's not really clear. If you click Yes to the message, your workbook size on disk will be smaller because the cache(s) are thrown out, but it will take longer to open the WB because Excel will rebuild each and every one as it opens, then refresh all the pivots."

It doesn't resolve the annoyance, but it puts my mind at ease that nothing is lost whether you click Yes or No.

Answer 2

Hi Jan Karel,

Could you be more specific about VBA code eliminating this silly message?

Thanks, Roman.

Answer 3

Hi,

I think you're right, this is a rather silly message. But apart from writing a bit of VBA code I see no way around it.