Troubleshooting
Problem
User #1 creates/runs an Excel spreadsheet report, which contains many Controller formulae.
- After running the report, the values/entries look correct (for example the names of accounts correctly appear.
- User #1 saves this as an XLSX file
User #2 opens that same Excel spreadsheet file (typically because it has been emailed to them)
- The user expects that many of the cells should contain values/data/figures from the Controller system
However, when User #2opens this file (on their own PC), they find that the data/values have disappeared.
- Specifically, Microsoft Excel has automatically recalculated all of the formulae (for example 'fGetVal') and so each of these cells returns the value zero (0) or #NAME
Symptom
A typical scenario where this is seen is:
- User 1 launches Excel/Controller, and runs a spreadsheet report. This populates the spreadsheet (XLS / XLSX file)
- User 1 then sends (for example via email) the spreadsheet file (XLS / XLSX file) to a colleague who does not have Controller installed (or running)
- User 2 opens the XLS/XLSX file inside Microsoft Excel.
- Immediately all the values (which were originally in the spreadsheet) disappear (either changed to '#NAME' or changed to zero / 0 / blank)
Cause
There are several potential causes for similar behaviour (values being changed to zero and/or #NAME appearing inside cells), for example:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Scenario #1 - The user opening the spreadsheet is running a different version of Microsoft Excel from the user who last saved the spreadsheet
- Example A: (most likely) - The spreadsheet was last saved using an earlier version of Excel (compared with the version of Excel that the user opening the file is using)
- Example B: (rare) - The spreadsheet was was created in Excel 2007 with the native Excel 2007+ format (XLSX), but is being opened inside an earlier version of Excel (for example Excel 2003). See separate IBM Technote #1405957.
More Information
In this case, the problem is caused by the design of Microsoft's Excel product.
By Microsoft design, this automatic recalculation will also be triggered if the user opens the spreadsheet in a different version of Excel (from the version that it was last saved with).
Unless the Excel session is connected/logged-into Controller, these recalculations will cause the cell value data to be lost (for example, change to zero or #NAME).
In this case, the problem is caused by the design of Microsoft's Excel product.
- By default, modern versions of Excel set its 'calculation mode' to 'Automatic':
- This will make Excel recalculate/re-run all sheets in all open workbooks, with every change made.
- This is done to ensure that all changes are updated across different sheets
By Microsoft design, this automatic recalculation will also be triggered if the user opens the spreadsheet in a different version of Excel (from the version that it was last saved with).
Unless the Excel session is connected/logged-into Controller, these recalculations will cause the cell value data to be lost (for example, change to zero or #NAME).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Scenario #2 - End user (who opens the spreadsheet) does not have the Controller link installed on their PC, but still wants to be able to open Controller XLS spreadsheets to read the figures.
More Information:
By Microsoft's intended design, all versions of Microsoft Excel (from Office 2000 onwards) will automatically recalculate formulas (with a 'Full Calculation' rather than a 'smart recalculation') when opening a .XLS file which was last saved by an earlier version of Excel.
- This causes the spreadsheet to attempt to recalculate/re-run the Controller formulae when the end user opens the spreadsheet.
- However, since they do not have the Controller Excel link installed, this re-calculate will fail, and so the end user will be presented with zero values and #NAME etc.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Scenario #3 - Problem triggered by opening Data Entry form (or Report Generator report)
It is possible that users may see similar symptoms when opening Data Entry forms (and Report Generator reports) from within the main Controller application:
- For full details, see separate IBM Technote #1504410.
Environment
Scenario #1
Scenario #2
End user's PC does not have the Controller Excel link installed.
Typically seen where the Controller Excel spreadsheet was originally saved on a system which was running an earlier version of Microsoft Excel.
- For example, the source PC was running an older version of Excel, and the target PC (that opens the spreadsheet) is running a later version of Microsoft Excel.
Scenario #2
End user's PC does not have the Controller Excel link installed.
Resolving The Problem
Scenario #1 & #2 
1. Logon to the recipient client device (the device which is opening the Excel files)
2. Launch Windows Registry Editor (START - RUN - "REGEDIT")
3. Navigate to the following registry key: HKEY_CURRENT_USER\Software\Microsoft\Office\<OfficeVersion>\Excel\Options
...where <OfficeVersion> relates to the version of Excel installed:
There are two methods to solve the problem:
Method #1 - Recommended if you only have a small number of spreadsheets (XLS/XLSX files), or you have a large number of recipients.
- Modify each and every Excel spreadsheet so that it uses "manual recalculation".
NOTE: The following must be done on a per-spreadsheet basis:
1. Open the spreadsheet XLS file on the original (source) PC
2. Inside Excel, open the 'Options' menu item. The method to achieve this will vary depending on the version of Excel installed
2. Inside Excel, open the 'Options' menu item. The method to achieve this will vary depending on the version of Excel installed
- For example, with Excel 2016 click "File - Options"
3. Open the relevant menu item. The name varies depending on version of Excel:
- For some versions it is a tab called 'calculation'
- For Excel 2016 it is the menu item 'Formulas'
4. Change the 'Workbook Calculation' setting (from the default = 'automatic') to 'Manual':
5. Untick the box 'Recalculate workbook before saving'
6. Save the XLSX spreadsheet.
6. Save the XLSX spreadsheet.
- The XLSX spreadsheet is now ready to be sent to the other colleague(s) (it will no longer try to refresh itself when opened up.
Method #2 - Recommended if you have a large number of spreadsheets to send (or a small number of recipients)
Modify the behaviour of the client device's installation of Microsoft Excel, so it does not automatically recalculate formulae when opening files last saved by an earlier version of Excel
- NOTE: The following is based on information from a Microsoft (third party / non-IBM) website. It refers to third-party (non-IBM) Microsoft software. It is therefore used by customers at their own risk.
1. Logon to the recipient client device (the device which is opening the Excel files)
2. Launch Windows Registry Editor (START - RUN - "REGEDIT")
3. Navigate to the following registry key: HKEY_CURRENT_USER\Software\Microsoft\Office\<OfficeVersion>\Excel\Options
...where <OfficeVersion> relates to the version of Excel installed:
- Office 2000 = 9.0
- Office XP = 10.0
- Office 2003 = 11.0
- Office 2007 = 12.0
- Office 2010 = 14.0
- Office 2013 = 15.0
4. Insert a new DWORD value:
- Value name: FullCalcOnLoadOldFile
- Value type: REG_DWORD
- Value data: 0
Scenario #3
Use the "EXCELCALCMANUAL" local preference.- For full details, see separate IBM Technote #1504410.
Related Information
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"ARM Category":[{"code":"a8m0z000000bpIoAAI","label":"Excel"}],"ARM Case Number":"TS003603994","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
1041110
Was this topic helpful?
Document Information
Modified date:
20 April 2020
UID
swg21367273