Why won't my PowerPivot for Excel 2010 workbook upgrade to Excel 2013?

[mssql-support]

First published on MSDN on May 23, 2013
Recently we've seen an issue where some Excel 2010 workbooks containing PowerPivot models encounter errors when attempting to upgrade to Excel 2013.

When opening a PowerPivot model that was created in Excel 2010 in Excel 2013 you will be prompted to upgrade with the following message:

This workbook has a PowerPivot data model created using a previous version of
the PowerPivot add-in. You will need to upgrade this data model with PowerPivot
for Excel 2013.

After clicking OK to upgrade to model the following error message is displayed:

============================
Error Message:
============================

The handle is invalid
----------------------------
The '' local cube file cannot be opened.
----------------------------
A connection cannot be made. Ensure that the server is running.
----------------------------
Sorry, PowerPivot couldn't connect to server A connection cannot be made. Ensure that the server is running..

============================
Call Stack:
============================

at Microsoft.AnalysisServices.LocalCubeStream..ctor(String cubeFile, OpenFlags settings, Int32 timeout, String password, String serverName)
----------------------------
at Microsoft.AnalysisServices.LocalCubeStream..ctor(String cubeFile, OpenFlags settings, Int32 timeout, String password, String serverName)
at Microsoft.AnalysisServices.XmlaClient.OpenLocalCubeConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
----------------------------
at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId, ObjectExpansion expansionType)
at Microsoft.AnalysisServices.BackEnd.DataModelingSandboxConnection.OpenAMOConnection()
----------------------------
at Microsoft.AnalysisServices.BackEnd.DataModelingSandboxConnection.OpenAMOConnection()
at Microsoft.AnalysisServices.BackEnd.DataModelingSandboxConnection.Open()
at Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadSandboxAfterConnection(String errorCache)
at Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadSafeSandboxAfterConnection(String errorCache)
at Microsoft.Office.PowerPivot.ExcelAddIn.InProcServer.LoadOLEDBConnection(Boolean raiseCompleteEvent, String errorCache)

============================



This issue occurs when a Pivot Table in the workbook has an invalid set or calculated field definition.

When the attempt to upgrade begins, Excel opens the PowerPivot model and executes the commands in the pivotcache for the pivot tables in the workbook, including creating session sets or calculated items. If a definition contains an error then the embedded PowerPivot engine returns an error and Excel disconnects from the PowerPivot embedded engine, returning the message saying the server connection could not be opened.



We are working with the Excel team to address this issue, but for now one option to work around this problem is to use the following steps:

1. Open the workbook in Excel 2010
2. Click on a PivotTable
3. In the PivotTable Tools, Options, menu click on the "Fields, Items, and Sets" button and choose Manage Sets.
4. Delete any invalid set definitions.
5. Save the file then open it in Excel 2013.  It should upgrade successfully.

-Wayne Robertson