Change links without updating them
 

Excel Version: 2003

I am using cells.replace to change some external links. For example, a cell
might have "='\\myserver\mypath\[file1.xls]sheet1!a23'", and I replace the
"file1.xls" with "file2.xls". However, when I do this, the referenced file
is opened and the value updated, which can take some time because of the size
of the file and the fact that it is on the network. Is there a way prevent
the referenced file from opening and updating the value? I would like to
make all my changes, then just have the values updated all at the same time.
I have tried changing the setting in the "Startup Prompt" button on the Edit
Links window, and I have tried changing the Calculation and Workbook Settings
options on the Calculation tab of Tools-Options. Nothing seems to make a
difference.

Jay

Change links without updating them
 

Jay,

Try this: Start your macro with

With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With

and finish it with

With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With

HTH,
Bernie
MS Excel MVP


"Jay" wrote in message
...
Excel Version: 2003

I am using cells.replace to change some external links. For example, a cell
might have "='\\myserver\mypath\[file1.xls]sheet1!a23'", and I replace the
"file1.xls" with "file2.xls". However, when I do this, the referenced file
is opened and the value updated, which can take some time because of the size
of the file and the fact that it is on the network. Is there a way prevent
the referenced file from opening and updating the value? I would like to
make all my changes, then just have the values updated all at the same time.
I have tried changing the setting in the "Startup Prompt" button on the Edit
Links window, and I have tried changing the Calculation and Workbook Settings
options on the Calculation tab of Tools-Options. Nothing seems to make a
difference.

Jay

Change links without updating them
 

Bernie,

Thanks for the suggestion, but it didn't help any.

Jay

"Bernie Deitrick" wrote:

Jay,

Try this: Start your macro with

With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With

and finish it with

With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With

HTH,
Bernie
MS Excel MVP


"Jay" wrote in message
...
Excel Version: 2003

I am using cells.replace to change some external links. For example, a cell
might have "='\\myserver\mypath\[file1.xls]sheet1!a23'", and I replace the
"file1.xls" with "file2.xls". However, when I do this, the referenced file
is opened and the value updated, which can take some time because of the size
of the file and the fact that it is on the network. Is there a way prevent
the referenced file from opening and updating the value? I would like to
make all my changes, then just have the values updated all at the same time.
I have tried changing the setting in the "Startup Prompt" button on the Edit
Links window, and I have tried changing the Calculation and Workbook Settings
options on the Calculation tab of Tools-Options. Nothing seems to make a
difference.

Jay