PDA

View Full Version : How to detect "no source data" on pivottable



JWhite
10-30-2008, 09:02 AM
Under Pivottable Options/Data the user can uncheck the "Save Source Data with File". The default is to save the source data, that is, the pivotcache data underlying the pivottable will be saved with the workbook.

For large pivotcaches the user may choose to NOT save the source data. The effect is that the workbook saves and loads faster because it's smaller but the next time they open the workbook they have to refresh the pivottable to re-load the cache from its source file.

If they try to do anything with the pivottable BEFORE refreshing they get an error. I don't want them to get an error so I need to know whether the pivotcache is there or not.

So far the only way I've been able to detect it is by checking the size of the pivotcache as follows:

PvCacheSize = Activeworkbook.pivotcaches(1).memoryused

This instruction errors if the data was not saved and has not been refreshed. It works but I don't like it. I hate to rely on an error to detect something and I'm not sure it's entirely reliable. Of course, I can check to see if the "Save Source Data with File" option has been de-selected but that doesn't tell me if the pivotcache is there when they're trying to run a report. Any suggestions?