We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

How do I disable the "Update Values:" dialoge box when Replacing linked values with a link location that doesn't exist yet?

Medium Priority
390 Views
Last Modified: 2012-05-06
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!!
Comment
Watch Question

Why not replace all your = (equal signs) with '= and convert them to text
.
.
then edit all your formulae .. links etc to whatever you want
.
.
and then again replace all '= with =

Ritesh
CERTIFIED EXPERT

Commented:
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
 

Sub Macro3()
    Application.DisplayAlerts = False
    ActiveWorkbook.ChangeLink "C:\test\main.xls", "C:\test\doesnt exist.xls"
    Application.DisplayAlerts = True
End Sub

Open in new window

Author

Commented:
riteshparakh, that didn't work, it won't change it back to a link. thanks anyway.

Author

Commented:
brettdj, this is a stupid question but where does the code snippet go? Do I start a macro to do this?
CERTIFIED EXPERT

Commented:
Sure. I should have provided this up front. From your workbook with the links
  1. Hit Alt & F11 to go to the VBE
  2. Insert - Module
  3. Copy and Paste the code I provided
  4. Update the relevant paths to the current link, and your future dummy link  (ie C:\test\main.xls is the current linked path)
  5. Hit Alt & F11 to go back to Excel
  6. Run the macro via Tools ... Macro .... Macros and click on the unimaginatively named Macro3
Cheers
Dave

Author

Commented:
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?
CERTIFIED EXPERT
Commented:
Sure :)
This more detailed code replaces "\main" with "\new" as a wildcard in all links
Cheers
Dave


Sub ReplaceLinks()
    Dim SourcePath As String
    Dim DummyPath As String, ls
    SourcePath = "\main"
    DummyPath = "\new"
    Application.DisplayAlerts = False
    For Each ls In ActiveWorkbook.LinkSources
        If InStr(ls, SourcePath) > 0 Then
            ActiveWorkbook.ChangeLink ls, Replace(ls, SourcePath, DummyPath)
        End If
    Next
    Application.DisplayAlerts = True
End Sub

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thank you so much, you just saved me hours of work!
CERTIFIED EXPERT

Commented:
No probs, thx for the grade :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.