View Single Post
 
Old 01-31-2017, 02:58 AM
Kev Kev is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Jan 2017
Posts: 7
Kev is on a distinguished road
Default

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
Reply With Quote