Glacial "Find and Replace"

[Guest]

1) Very large workbook >30M
2) There are extensive links to approx 80 other workbooks (not possible to open all at once
3) Find and Replace of "internally referenced" formulas fine, but very slow for formulas linked to other workbook
4) Takes approx 25 minutes to make about 8,000 "Find and Replace" change
5) Sample link: ='S:\Directory\Sub-directory1\Sub-directory2\[Ottawa.xls]GuardP&L'!$B$1
6) If there is no way to speed this up, can you suggest a good novel.

 

[jeff]

Stephen,

Wow. Sounds like you're trying to scrub an elephant with
a toothbrush!!

Are the 8,000 replacements all of them? or is this number
just an example and you really have 80,000 (=~5 hrs)?

Any way to:

1) perform any of the work in a tru database?
(export data from excel, manipulate, then reload it?)
2) break up the processing into smaller chunks?
3) get rid of old data & links not used anymore?
4) copy the remote files onto a different drive but
on the same machine?
5) get bigger/faster hardware?

Sorry, I'm fresh out of good ideas....but I feel for ya'
(Are you allowed to drink at work?)

jeff

-----Original Message-----
1) Very large workbook >30MB
2) There are extensive links to approx 80 other

workbooks (not possible to open all at once)

3) Find and Replace of "internally referenced" formulas

fine, but very slow for formulas linked to other workbooks

4) Takes approx 25 minutes to make about 8,000 "Find and Replace" changes
5) Sample link: ='S:\Directory\Sub-directory1\Sub- directory2\[Ottawa.xls]GuardP&L'!$B$13
6) If there is no way to speed this up, can you suggest a good novel.
.

 

[RagDyer]

Can't exactly remember what I did several years ago concerning the ability
to increase the linking speed of some very large WBs.

Go to,
<Tools> <Options> <Calculation> tab,
And either check or uncheck (change what's there now),
"Save External Link Values".

Wait a couple of days and see if things get better or worse!
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

1) Very large workbook >30MB
2) There are extensive links to approx 80 other workbooks (not possible to
open all at once)
3) Find and Replace of "internally referenced" formulas fine, but very slow
for formulas linked to other workbooks
4) Takes approx 25 minutes to make about 8,000 "Find and Replace" changes
5) Sample link:
='S:\Directory\Sub-directory1\Sub-directory2\[Ottawa.xls]GuardP&L'!$B$13
6) If there is no way to speed this up, can you suggest a good novel.

 

[Charles Williams]

Hi Stephen,

The reason why it is so much slower with external links is that when you
change a link formula Excel has to read through the closed file to find the
current value.

Some things to try:

- open a subset of the other workbooks, do the find and replace for links to
that subset only, then repeat for the next subset. (This will definitely be
fast)
- use Edit-->Links-->Change Source (this might be faster)

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

1) Very large workbook >30MB
2) There are extensive links to approx 80 other workbooks (not possible to open all at once)
3) Find and Replace of "internally referenced" formulas fine, but very

slow for formulas linked to other workbooks

4) Takes approx 25 minutes to make about 8,000 "Find and Replace" changes
5) Sample link: ='S:\Directory\Sub-directory1\Sub-directory2\[Ottawa.xls]GuardP&L'!$B$13
6) If there is no way to speed this up, can you suggest a good novel.

 

[BrianB]

I think you have done well to get this working anyway.
I assume you set calculation to Manual ? This would solve oceans o
time.

<<6) If there is no way to speed this up, can you suggest a goo
novel.>>
"War and Peace" is often suggested at such times.
I do not recommend my usual "coffee" method - it could do more har
than good in your case. <BG>