Troubleshooting
Problem
User opens a spreadsheet (XLSX) file. User clicks 'Ctrl - F' on their keyboard, and clicks on the tab 'Replace'. Inside 'Find what' and 'Replace with' user enters the relevant values. User clicks 'Replace All'. This quickly (almost immediately) displays the number of changes that Excel will make (for example 1000). User then clicks OK. User then tries to click 'Close'. However, it now takes a long time (for exampe 25 seconds) before Excel is usable again (during which the Excel cells may flicker, as if they are updating themselves).
Symptom
In one real-life example, it took around 25 seconds to replace the contents of 1061 cells.
- This compares with a near-instantaneous change when the Controller link is disabled (inactive).
More Information
The symptoms vary slightly depending on which exact version of Controller you are using:
- 10.2.5110.85 - The slowness/delay is reproduced whether-or-not you are logged into the Controller Excel-link
- 10.2.5110.95 - The slowness/delay is *only* reproduced if you are logged into the Controller Excel-link. In other words, if (inside Excel) you first click "Controller - Logout", then the 'Find and Replace' task runs quickly.
Cause
Known limitation (reference APAR PI49546) in Controller.
Environment
Problem has been seen in Excel 2007, 2010 and 2013.
Resolving The Problem
Fix:
This is a known limitation. IBM are unable to modify the design of Controller to avoid this limitation.
=> It will not be fixed in a future release of Controller.
Workaround:
Method #1 - only suitable for Controller 10.2.5110.95 and later
Temporarily logoff (from Controller's Excel link) before performing the 'Find / Replace' operation.
- In other words, inside Excel, click "Controller - Log Off":
Method #2 - suitable for all versions of Controller
Disable the Controller Excel link before performing large 'Find / Replace' operations.
Steps:
1. Launch Excel
2. Click "Excel button" (or 'File' - depending on the version of Excel) and then 'Options'
3. Click 'Add-ins'
4. Change 'Manage' to 'COM Add-ins':
5. Click 'Go'
6. Untick the box 'Cognos Controller Link for Microsoft Excel'
7. Perform the required Find/Replace actions (in the spreadsheets)
8. Afterwards, re-enable the Controller add-in by ticking this box:
Method #3 - suitable for all versions of Controller
Temporarily disable Microsoft Excel feature 'VBA Events' (before using the Find/Replace functionality) by using a macro.
Steps:
The following instructions are based on Excel 2013. There will be slight differences in procedure if using a different version of Excel.
1. Launch Excel
2. Open the 'slow' Controller spreadsheet
3. Click 'Alt+F11' (on keyboard) to open the Visual Basic Editor
4. Right-click on 'ThisWorkbook' (inside your spreadsheet) and choose 'Insert - Module':
5. Copy the following code (below) into the blank/white sheet ("Module1 (Code)")
- Sub DisableEventsForController()
Application.EnableEvents = False
End Sub
Sub EnableEventsForController()
Application.EnableEvents = True
End Sub
6. Press 'save' icon
7. Close Visual Basic Editor
Test:
8. To speed up the find/replace, beforehand simply:
- Click 'Alt-F8' (on the keyboard)
- Double-click on 'DisableEventsForController':
9. When you are finished using find/replace, re-enable Events by simply:
- Click 'Alt-F8'
- Double-click on 'EnableEventsForController'.
Related Information
Was this topic helpful?
Document Information
Modified date:
28 November 2018
UID
swg21967538