Problem

When opening an Excel file containing a pivot table that was streamed to an IE client, Excel will throw the following error message:

Cannot open PivotTable source file ‘[filename[1].xls]SourceData’

This is caused by IE caching the streamed file with a temporary name containing brackets (e.g. filename[1].xlsx). Since Excel does not allow pivot table data sources to contain brackets, when the cached file is opened, the pivot tables try to refresh using the temporary file name.

Solution

There are several ways to work around this:

• Use a non-IE browser, such as Firefox or Chrome
• Use IE 9 or later – this appears to have been resolved in the newer versions of IE
• Save the file to disk before opening it to avoid opening the file with a temporary name
• Stream the file inline instead of an attachment. This will open the file in IE’s Excel plugin instead of downloading it. If you are using IE 7 or later, you will need to configure the registry on the client to use this option.
• Add Excel macros to the report to dynamically rename the PivotTable to exclude the bracket and then refresh the PivotTable. An example of these macros is available in this blog post.

Note: This is not an issue with the OfficeWriter product. This behavior occurs anytime someone attempts to download and open an Excel workbook with a PivotTable directly from certain versions of IE.

We have made these solutions available online because our customers have found the information helpful. However, these solutions are not covered under OfficeWriter product support. If you experience issues implementing any of these solutions, we encourage you to reach out to the appropriate vendor (i.e. Mozilla for Firefox, Google for Chrome, and Microsoft for IE 9 (or later) or to configure the in-line plugin).

In particular, if you encounter difficulties with the macro solution, feel free to leave a reply on the blog post so that the author of the post can address your concerns.

Follow

Related posts: