Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
links not updating | Links and Linking in Excel | |||
Updating links | Excel Worksheet Functions | |||
change file links or updating formulas | Excel Programming | |||
updating links from VB | Excel Programming | |||
Writing a macro to change external links to manual updating in Excel 2000 | Excel Programming |