Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q: Changing a link without link validation
Hello,
I have a script that changes an Excel link from one Excel document to another. How do I supress Excel's link validation process? The new link will not be available until after more file migration has completed. Example: File ABC.XLS, cell A1 contains - ='C:\users\mike\[remote.xls]Sheet1'!A1 and I want to change it to - ='D:\mike\[remote.xls]Sheet1'!A1 But the file D:\mike\remote.xls does not exist at the time of the change being made to the file ABC.XLS. All of my automation is working, but Excel prompts me with a dialog box asking me to locate D:\mike\remote.xls. How do I supress this dialog box? TIA! -- Mike Frederick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q: Changing a link without link validation
Application.displayalerts = false
Rember that when this is off no display alerts come up such as do you want to save over that file. It just does it. So I recomend that as soon as you can yuo should turn them back on again. Jase -----Original Message----- Hello, I have a script that changes an Excel link from one Excel document to another. How do I supress Excel's link validation process? The new link will not be available until after more file migration has completed. Example: File ABC.XLS, cell A1 contains - ='C:\users\mike\[remote.xls]Sheet1'!A1 and I want to change it to - ='D:\mike\[remote.xls] Sheet1'!A1 But the file D:\mike\remote.xls does not exist at the time of the change being made to the file ABC.XLS. All of my automation is working, but Excel prompts me with a dialog box asking me to locate D:\mike\remote.xls. How do I supress this dialog box? TIA! -- Mike Frederick . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a link without link validation
OK, a little more info. The problem is that the formula being replaced
looks like =VLOOKUP(C3,"C:\users\mike\data.xls",... So I guess what I need to do is somehow turn off VLOOKUP evaluation during my update. I tried doing this manually with Tools-Options/Calculation set to "manual" and clearing all the other settings, but this didn't fix it. It still tries to find the new file listed in the VLOOKUP function when I replace the contents of the cell. -- Mike Frederick "Mike Frederick" wrote in message ... OK, that seems to work; the dialog boxes are suppressed. But what about stopping the actual attempt that Excel makes at validating the reference? My script works, but in order to change 68 links in one file the script takes 10 minutes! Watching the script run shows a 20 second delay upon each link change. Is there a way to avoid the link validation? TIAA "Thanks In Advance Again" -- Mike Frederick "Jase" wrote in message ... Application.displayalerts = false Rember that when this is off no display alerts come up such as do you want to save over that file. It just does it. So I recomend that as soon as you can yuo should turn them back on again. Jase -----Original Message----- Hello, I have a script that changes an Excel link from one Excel document to another. How do I supress Excel's link validation process? The new link will not be available until after more file migration has completed. Example: File ABC.XLS, cell A1 contains - ='C:\users\mike\[remote.xls]Sheet1'!A1 and I want to change it to - ='D:\mike\[remote.xls] Sheet1'!A1 But the file D:\mike\remote.xls does not exist at the time of the change being made to the file ABC.XLS. All of my automation is working, but Excel prompts me with a dialog box asking me to locate D:\mike\remote.xls. How do I supress this dialog box? TIA! -- Mike Frederick . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing the filename in a Link | Excel Discussion (Misc queries) | |||
Changing link target | Excel Discussion (Misc queries) | |||
Link from Autoshape keeps changing | Links and Linking in Excel | |||
changing a cell to changing the link | Excel Worksheet Functions | |||
Automatically changing link. How? | Excel Discussion (Misc queries) |