Thanks for any help.
I have an Excel worksheet that has 13 links to one other file. The links are
in form of file name, worksheet name, & cell address in the cells of this
file. Like:
=('M:\PRODUCTION\LCA-ACTL\LIBRARY\ECF Reports\2005-08\[ECF -
08-19pm.xls]Main Portfolio'!G22)/1000
I also have a macro that changes the file in the link, using search and
replace:
Cells.Replace What:=c, Replacement:=H, LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
For example, c='[ECF - 08-19pm.xls]' and H='[ECF - 08-20pm.xls]'.
My problem is it takes about 30-45 seconds for the macro to replace the
whole 13 links. I can see each cell blank out one by one slowly, then after
they have all blanked out, they are updated with the info in the other file.
I figure I must be doing something inefficiently, is there a way I can speed
this up? Any help appreciated.

You could add Application.ScreenUpdating = False to the begining of
your code and then Application.ScreenUpdating = True at the end. This
might save a little time.

Have your workbook open that other file, do the edit|Replace (in code), then
close the other workbook.

Ian Elliott wrote:

Thanks for any help.
I have an Excel worksheet that has 13 links to one other file. The links are
in form of file name, worksheet name, & cell address in the cells of this
file. Like:
=('M:\PRODUCTION\LCA-ACTL\LIBRARY\ECF Reports\2005-08\[ECF -
08-19pm.xls]Main Portfolio'!G22)/1000
I also have a macro that changes the file in the link, using search and
replace:
Cells.Replace What:=c, Replacement:=H, LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
For example, c='[ECF - 08-19pm.xls]' and H='[ECF - 08-20pm.xls]'.
My problem is it takes about 30-45 seconds for the macro to replace the
whole 13 links. I can see each cell blank out one by one slowly, then after
they have all blanked out, they are updated with the info in the other file.
I figure I must be doing something inefficiently, is there a way I can speed
this up? Any help appreciated.

--

Dave Peterson