Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

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!!
0
stadiumred
Asked:
stadiumred
  • 4
  • 4
1 Solution
 
riteshparakhCommented:
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
0
 
Dave BrettVice President - Business EvaluationCommented:
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

0
 
stadiumredAuthor Commented:
riteshparakh, that didn't work, it won't change it back to a link. thanks anyway.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
stadiumredAuthor Commented:
brettdj, this is a stupid question but where does the code snippet go? Do I start a macro to do this?
0
 
Dave BrettVice President - Business EvaluationCommented:
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
0
 
stadiumredAuthor 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?
0
 
Dave BrettVice President - Business EvaluationCommented:
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

0
 
stadiumredAuthor Commented:
Thank you so much, you just saved me hours of work!
0
 
Dave BrettVice President - Business EvaluationCommented:
No probs, thx for the grade :)
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now