"Standard Error ... -2147467161 ... interop.Excel ... Attempted to read or write..." when using Excel 2007 or 2010
User opens a spreadsheet (XLSX file) and clicks F9 to run it. User receives Error #1. After clicking OK, user receives Error #2. After clicking OK, user receives Error #3.
Description: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
at Excel._Worksheet.set_EnableCalculation(Boolean RHS)
at Cognos.Controller.Reports.XlLinkLayout.RecalcXL(Int32 lVersion, Boolean bActiveSheetOnly)
There are several possible causes for similar errors.
- TIP: For more examples, see separate IBM Technote #1665528.
This Technote specifically relates to the scenario where the cause is that the Controller client has run out of memory, triggered by the spreadsheet report using an incorrect formula similar to:
Specifically, the customer is not using the correct parameter syntax in the report.
By design, Controller (when using Excel 2007 or later Excel version) is has been modified to be able to handle double byte characters.
- However, for this new functionality to work the text parameters must be surrounded with "".
- For example, by changing the formula to =cc.fText("1";"2") it will work.
Therefore, in one real-life customer case, the customer received this error/problem after upgrading from Excel 2003 to Excel 2007.
Client device using Excel 2007 or 2010.
Resolving the problem
Modify the cc.fText (in the report/report layout) formulae to contain double quotes ("") around each parameter.
For example, here is a valid (correct) example:
- Redesign the report to exclude the "Contribution Version" and "Closing Version" in the columns.
- or use Excel 2003 (instead of Excel 2007 or Excel 2010).