stadiumred
asked on
How do I disable the "Update Values:" dialoge box when Replacing linked values with a link location that doesn't exist yet?
I'm trying to replace cells in my worksheet that are linked to a worksheet that has yet to be created. The problem is that when I use the find replace function each cell that has the link changed to a nonexistant file yeilds an "Update Values:" dialogue box that prompts me to link the value to an existing workbook. Is it possible to turn off or diable this "Update Values:" dialogue box so that I just be the #REFs for now? Creating dummy files is not an option as there are too many that would need to be done. Thanks!!
You can do this with code and suppress the warning with Application.DisplayAlerts set to False
for example, if you have valid workbook links to
C:\test\main.xls
you could replace them with this code that links to a non-existent workbook without any prompts
Cheers
Dave
for example, if you have valid workbook links to
C:\test\main.xls
you could replace them with this code that links to a non-existent workbook without any prompts
Cheers
Dave
Sub Macro3()
Application.DisplayAlerts = False
ActiveWorkbook.ChangeLink "C:\test\main.xls", "C:\test\doesnt exist.xls"
Application.DisplayAlerts = True
End SubASKER
riteshparakh, that didn't work, it won't change it back to a link. thanks anyway.
ASKER
brettdj, this is a stupid question but where does the code snippet go? Do I start a macro to do this?
Sure. I should have provided this up front. From your workbook with the links
Dave
- Hit Alt & F11 to go to the VBE
- Insert - Module
- Copy and Paste the code I provided
- Update the relevant paths to the current link, and your future dummy link (ie C:\test\main.xls is the current linked path)
- Hit Alt & F11 to go back to Excel
- Run the macro via Tools ... Macro .... Macros and click on the unimaginatively named Macro3
Dave
ASKER
thanks that makes sense! Is there a way to do a find/replace in that macro? For example C:\test\main.xls may have 12 variations, C:\test\main1.xls, C:\test\main2.xls, C:\test\main3.xls etc and I want them to just change out "main" with "new" and keep the number after it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much, you just saved me hours of work!
No probs, thx for the grade :)
.
.
then edit all your formulae .. links etc to whatever you want
.
.
and then again replace all '= with =
Ritesh