slow replace function
 

I have excel sheet which has lots of links to other workbooks. The links
need to be updataed every week.

For example: a cell linked to "c:\0626\[xx_0626.xls]'sheet0626'!A3"
should be updated to "c:\0703\[xx_0703.xls]'sheet0703'!A3". I tried to
use the "replace" function to replace "0626" with "0703". It works if
there are only a few links to update. However, it takes forever if
there are hundreds of links. I don't know why the replace function is
so slow in Excel. I did try to turn off the auto calculation, but it
doesn't help. Anybody knows a faster way to do such update?
Thanks a lot!


--
hiwan99
------------------------------------------------------------------------
hiwan99's Profile: http://www.excelforum.com/member.php...o&userid=24936
View this thread: http://www.excelforum.com/showthread...hreadid=384722

Have you tried doing the "replace" when both OldFile & NewFile are open?
I think that will speed things up.
Rgds,
ScottO

"hiwan99" wrote in message
...
|
| I have excel sheet which has lots of links to other workbooks. The links
| need to be updataed every week.
|
| For example: a cell linked to "c:\0626\[xx_0626.xls]'sheet0626'!A3"
| should be updated to "c:\0703\[xx_0703.xls]'sheet0703'!A3". I tried to
| use the "replace" function to replace "0626" with "0703". It works if
| there are only a few links to update. However, it takes forever if
| there are hundreds of links. I don't know why the replace function is
| so slow in Excel. I did try to turn off the auto calculation, but it
| doesn't help. Anybody knows a faster way to do such update?
| Thanks a lot!
|
|
| --
| hiwan99
| ------------------------------------------------------------------------
| hiwan99's Profile: http://www.excelforum.com/member.php...o&userid=24936
| View this thread: http://www.excelforum.com/showthread...hreadid=384722
|

Thanks, ScottO!
I did try to do the replace while both files are open. But it doesn't
help. At least for me.


--
hiwan99
------------------------------------------------------------------------
hiwan99's Profile: http://www.excelforum.com/member.php...o&userid=24936
View this thread: http://www.excelforum.com/showthread...hreadid=384722

Maybe your CPU's battling a large calculation overhead. Try switching to
manual calculation before the replace.
ScottO

"hiwan99" wrote in
message ...
|
| Thanks, ScottO!
| I did try to do the replace while both files are open. But it doesn't
| help. At least for me.
|
|
| --
| hiwan99
| ------------------------------------------------------------------------
| hiwan99's Profile:
http://www.excelforum.com/member.php...o&userid=24936
| View this thread: http://www.excelforum.com/showthread...hreadid=384722
|