All is not quiet on the home front…
“Cannot open PivotTable source file” – If you have ever encountered this error message, then you know the frustrations of The Bracket Problem. If you haven’t run into this issue before, let me paint you a picture:
There some sort of pivot report on a server, for example, a report that’s being dynamically generated for Excel using OfficeWriter. An end user decides to download the report on her work machine with IE 7. When she opens the workbook, instead of the pivot table refreshing, she gets a warning message:
Cannot open PivotTable source file: ‘C:\Users\Pitan\AppData\Local\Microsoft\Windows\Temporary Internet Files\….\ [PitanPivotReport.xlsx]Sheet1’.
When she tries downloading the report using Mozilla FireFox, the warning message doesn’t occur.
This, my friends, is The Bracket Problem.
What trickery is afoot?
The Bracket Problem is caused when a pivot report is downloaded with IE because IE caches the file with a unique name that contains brackets (e.g. PitanPivotReport.xlsx), but Excel doesn’t allow pivot data source references to contain brackets.
You can actually reproduce The Bracket Problem by creating a pivot table, closing the workbook, renaming the workbook to have a bracket in the name, reopen the workbook and trying to refresh the pivot table.
For more information on the Bracket Problem, please refer to Microsoft’s KB article.
Today I’ll outline (with Pitan’s help) several workarounds for this problem.
The Quest Begins:
Option 1: Use a non-IE browser:
If another browser is used, such as Firefox or Chrome, the error message doesn’t appear when the workbook is opened directly from the browser. Obviously, this apporach won’t work if your end users are using Internet Explorer.
Option 2: Use IE 9:
The Bracket Problem does not happen when the workbook is opened with Internet Explorer 9. Unfortunately, IE 9 is only available on the newer operating systems (Vista, Windows 7), so that still leaves the problem with those using IE 8 and below. For that, we’ll need a bit of pivot magic.
Option 3: Stream the Excel file inline instead of as an attachment:
When the Excel file is opened directly in the browser, through an Internet Explorer plugin, the error message does not occur because the file is not cached with a bracketed naming scheme. Other browsers won’t have the IE plugin, but can still open the workbook as an attachment without the warning message appearing.
The only problem is that this doesn’t work with IE 7 and IE 8 because starting with version 7, IE no longer opens Office files directly in the browser by default.
You can do a little registry magic to configure Internet Explorer to open Office files in the browser instead of opening a new window. The link is to Microsoft’s KB article that explains how to enable the in-browser behavior in IE 7 and IE 8.
Option 4: Save the downloaded file to disk first:
Save the file to disk before opening by clicking ‘Save’ instead of ‘Open’ in the download dialog. This saves the workbook with the proper name and the pivot data data source can update correctly.
Unfortunately, both of these workarounds are dependent on the end-user behavior or specific client machine configurations to avoid the issue. We’ll have to pull out the big guns to get this done.
Xtreme Pivot Spells
Option 5: Use macros to rename the file and refresh the pivot table cache when the file opens.
This 2-step approach works across different browsers (since the macros are in the Excel file) and doesn’t conflict with IE 9.
Note: If you’ve never had the pleasure of working with macros in Excel 2007 or 2010, I’ve blogged about the new macro security settings .
Step 1: Rename the workbook when it opens.
One of my colleagues, Ben Jones, has written some macro code that dynamically renames the workbook with all the brackets removed from the name:
This is the ReplaceText() helper method used in Rename():
Step 2: Update the pivot table data source to remove any cached references to the bracketed name
ven after renaming the workbook, the Bracket Problem persists unless you update the pivot table data source cache. Updating the pivot table data source is important because the data source will have cached the bracketed naming scheme, which will cause the error when the pivot table is refreshed.
It turns out that there are differences in how pivot operations happen in Excel 2003 and Excel 2007+, so I’ve outlined how to handle both scenarios.
Excel 2003: In Excel 2003, all pivot operations are performed through the pivot wizard, which is reflected in the macros that run in Excel 2003. The code to update and refresh a pivot table in Excel 2003 is below:
Excel 2007: Starting with Excel 2007, a new method to update and refresh a pivot cache was introduced, ChangePivotCache. This will work in Excel 2007/2010 on version 10 and version 12 pivot tables:
In order to make sure that we use the correct version, I whipped up a little method that figures out the version of Excel being used. In particular, if Excel 2003 is being used, it won’t try to update version 12 pivot tables.
All that’s left is the call Rename() and UpdateRefreshPTCache() in the Workbook_Open() routine:
Congratulations! You’ve reached your destination!
Hopefully one of these methods works for you. If I come across any hidden spells for this problem, I’ll definitely be sure to blog about them, but until then: