Error: “Essbase has encountered an internal Excel error” When Retrieving in Excel With Essbase Excel Add-In 11.1.1.3

Applies to:

 
Hyperion Essbase – Version: 11.1.1.3.00 and later [Release: 11.1 and later ]
Information in this document applies to any platform.
Confirmed Bug Status on 07-Mar-2011

Symptoms
Error: “Essbase has encountered an internal Excel error. Your sheet will not be overwritten and none of your data will be lost. However, you are strongly urged to exit Excel and restart in order to clear up the problem”

This error occurs when retrieving in Excel with Essbase Excel Add-In 11.1.1.3.

Cause
The Excel Add-in can only support the number of columns that Excel 2003 supports.

This issue has been verified unpublished Bug 9160216 – Internal Essbase error when lock or retrieve section on complicated spreadsheet.

Solution
This has been documented in the 11.1.1.3.5 readme:
Spreadsheet Add-in. When using Spreadsheet Add-in on Microsoft Office, performing a retrieve or lock on a particularly complex worksheet can cause an internal Excel error. Users may see an error message such as this:
“Essbase has encountered an internal Excel error. Your sheet will not be overwritten and none of your data will be lost. However, you are strongly urged to exit Excel and restart in order to clear up the problem.”
To avoid this situation, Oracle recommends that you take steps to reduce the complexity of the worksheet. For example, if your worksheet contains large ranges which are not required, reduce ranges by deleting or hiding unnecessary columns or rows.

The following workaround may also resolve the issue:

1. Open your Excel Workbook.
2. Go to File -> Save As and save it as an ‘Excel 97-2003 Workbook’.
3. Close and re-open this newly saved file.
4. Go to Files -> Save As and save it back as an ‘Excel Workbook’ (this will reset it as an Excel 2007 book).

You should now be able to retrieve without errors.

Another option is to use Smart View instead of the Excel Add-In.