Bulk changing links to external workbooks that dont yet exist

[hosteen]

I need to change the text in a large number of links to external workbooks. This is a monthly task and sometimes the workbooks I reference have yet to be created. This means if I use Find and Replace to change part of the text string in the links the update values dialogue will appear and end the Find and Replace process. I tried using a macro to find and replace but this still results in me having to press cancel hundreds of times for all of the workbooks linked to that have not yet been created. Is there a way to suppress this dialogue box. I’ve searched extensively but the solutions offered have not worked. Happy with a VBA solution or anything to be honest.

 

[hosteen]

Ah, I've solved it, in case anyone else is looking, I found this solution elsewhere but I think it originally came from somewhere within Mr Excel.

Made bold the Pertinent bits of code

Sub FindReplaceAll()

ThisWorkbook.UpdateLinks = xlUpdateLinksNever
Application.DisplayAlerts = False

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant

fnd = "201809"
rplc = "201810"

For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace what:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next sht


ThisWorkbook.UpdateLinks = xlUpdateLinksAlways
Application.DisplayAlerts = True

End Sub