Have a look at this simple vba - it searches every cell in a workbook and replaces the link - but it has only been told what to do with a link back to
one sheet in the source file.There may be links back to every sheet in the source.
As it stands it will loop through one file and replace the text 'D:\Documents\2016\[workdays_2016.xls]Sh03_2016'!" with 'D:\Documents\2017\[workdays_2017.xls]Sh03_2017'!" in every cell in the workbook
Provided the 2016sheetnames and 2017sheetnames are logically consistent, we should be able to adapt the VBA to make it loop through each file, each sheet and each link and search and replace all the links in the file in a single sweep.
So let's take one
real example with several sheets
1. What is the full name of the 2016 File?
2. What is the full name of the 2017 File?
3. Please list the full 2016 and 2017 equivalent names of all the sheets in the file like this:
2016 Sheet Name - equivalent 2017 Sheet Name
and I will amend the VBA to loop and then you can test it on that file.
Code:
Sub UpdateLink()
Dim wb As Workbook
Dim ws As Worksheet, wsTemp As Worksheet, cel As Range
Dim PathSheet2016 As String, PathSheet2017
Dim oldFormula As String, newFormula As String
'this is the workbook with the links
Set wb = Workbooks("Extract_2017.xls")
'2016 path, 2016 source file name,2016 sheet name
PathSheet2016 = "='D:\Documents\2016\[workdays_2016.xls]Sh03_2016'!"
'2017 path, 2017 source file name,2017 sheet name
PathSheet2017 = "='D:\Documents\2017\[workdays_2017.xls]Sh03_2017'!"
'loop through every used cell in every worksheet
For Each ws In wb.Worksheets
Set myrange = ws.UsedRange
For Each cel In myrange
oldFormula = cel.Formula
newFormula = Replace(cel.Formula, PathSheet2016, PathSheet2017)
cel.Formula = newFormula
Next cel
Next ws
End Sub