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[1].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[1].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.
Abracadabra….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.
Sub-Option 3-A:
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:
'Rename from the IE-provided bracket nameSub Rename() Dim newPath As String
newPath = ReplaceText(ReplaceText(ActiveWorkbook.FullName, "]", ")"), "[", "(")
If newPath <> ActiveWorkbook.FullName Then
ActiveWorkbook.SaveAs (newPath)
End If
End Sub |
This is the ReplaceText() helper method used in Rename():
' Replace all occurrances of from_str with to_str.Public Function ReplaceText(ByVal txt As String, ByVal from_str As String, ByVal to_str As String) As String Dim result As String
Dim from_len As Integer
Dim pos As Integer
from_len = Len(from_str)
Do While Len(txt) > 0
' Find from_str.
pos = InStr(txt, from_str)
If pos = 0 Then
' No more occurrences.
result = result & txt
txt = ""
Else
' Make the replacement.
result = result & Left$(txt, pos - 1) & to_str
txt = Mid$(txt, pos + from_len)
End If
Loop
ReplaceText = result
End Function |
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 2003 version of update/refresh (uses pivot wizard)Sub RefreshPT2003(ByVal PivotTableSheet As String, ByVal PivotTableName As String, ByVal PivotTableRange As String, ByVal PivotDataRange As String) 'Select a cell in the range that the pivot table occupies
ActiveWorkbook.Worksheets(PivotTableSheet).Activate
Range(PivotTableRange).Select
'Activate the pivot table wizard to change the data source
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
PivotDataRange
'Refresh the pivot table
ActiveSheet.PivotTables(PivotTableName).PivotCache.Refresh
End Sub |
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:
'Excel 2007+ version of update/refresh'Retains the PivotTable versionSub RefreshPT2007(ByVal PivotTableSheet As String, PivotTableName As String, PivotTableDataRange As String) 'Get the version of the pivot table
Dim PTVersion As XlPivotTableVersionList
PTVersion = ActiveWorkbook.Sheets(PivotTableSheet).PivotTables(PivotTableName).Version
'ChangePivotCache updates the data source and refreshes the pivot table
ActiveWorkbook.Sheets(PivotTableSheet).PivotTables(PivotTableName).ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PivotTableDataRange, Version:= _
PTVersion)
End Sub |
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.
Determines the version of Excel being used'Updates and refreshes the specified pivot tableSub UpdateRefreshPTCache(ByVal PTWorksheet As String, ByVal PTName As String, ByVal PTRange As String, PTDataRange As String) 'If the end user is using Excel 2007 or later, use the 'ChangePivotCache' approach
'PivotTable will retain version
If Application.Version >= 12# Then
RefreshPT2007 PTWorksheet, PTName, PTDataRange
'If the end user is using Excel 2003 or earlier
Else
'If the pivot table isn't version 10, don't attempt to refresh it
If ActiveWorkbook.Sheets(PTWorksheet).PivotTables(PTName).Version <> xlPivotTableVersion10 Then
'Do nothing
Else
'If the workbook is XLS, update the pivot table
RefreshPT2003 PTWorksheet, PTName, PTRange, PTDataRange
End If
End If
End Sub |
All that’s left is the call Rename() and UpdateRefreshPTCache() in the Workbook_Open() routine:
Private Sub Workbook_Open() Rename
' PivotTableSheet is the sheet with the pivot table on it
' PivotTableName is the name of the PivotTable (i.e. PivotTable1)
' PivotTableRange is a range where the pivot table is located (i.e. 'B10' or 'PivotTableRange')
' PivotTableDataRange is the name of the cell range for the data source of the pivot table
UpdateRefreshPTCache "Pivot Sheet", "PivotTable1", "PivotTableRange", "PivotData"
End Sub |
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:
Share the post "Pitan Pivot Mage and the Quest to Slay the Bracket Problem"
Follow