Here's an issue I've been trying to fix for a while now under the following scenario:
Simple excel file that contains at least 1 external reference in a lot of formulas (in the order of thousands)
All external reference paths are across closed workbooks located on network locations
What I need to do:
Change part of all external references. For example: change all instances of "January" to "February"
The problem:
No matter what method I used to find/replace strings within the external references, they're all slow (in the order of minutes) because apparently Excel checks if the external file exists before doing the actual find/replace and it does that for each and every related cell.
What I've tried so far, but to no avail:
- Change calculation mode to manual
- Disable "update links to other documents" when calculating this workbook (Within Excel advanced options)
- Disable "Automatic update of workbook links" (Within Trust Center -> External Content)
- Disable application events
- Disable screen updating
- Activeworkbook.changelink method
- Have source workbooks opened prior to doing replace
Question:
Is there any way to DISABLE this apparently built-in functionality of Excel which automatically checks if the external file exists for each and every cell when updating external references?
Bookmarks