changing link sources in excel-2010 files

Kev · #4 01-31-2017, 02:58 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Batch create word files with link to changing excel	hannes.ledegen	Mail Merge	8	04-07-2016 01:22 AM
Excel Office 2010 Files will not open outside of excel	stevetag	Office	3	06-22-2014 07:23 PM
Changing/ updating LINK from power point & excel	johnseito	PowerPoint	0	01-28-2014 06:31 PM
Excel 2010 changing external link formulas automatically on Webdav mapped drive	dirkswart	Excel	2	10-07-2013 12:44 PM
PPT 2010 will only insert, not link to audio files.	kevin3d	PowerPoint	1	10-07-2011 09:17 PM