changing link sources in excel-2010 files

mhagi · #1 01-26-2017, 03:31 AM

Hello all,

With years change one of our departments needs to rebuild files with new links. The old links refer to files of the then previous year and its folder. Now the folder has changed, the file name has changed and – unfortunately – the name of the worksheet
Example:
previous:
[path]\2016\[workdays_2016.xls]2016!a3
new:
[path]\2017\[workdays_2017.xls]2017!a3
I can change the file link successfully with

ActiveWorkbook.ChangeLink Name:=oldname, NewName:=newname
old and new name respectively are full file-name with path and extension
But I’m then prompted for the new sheet name.
Of course I could run thru all sheets and all formulas to change the formulas, but this is very time consuming - numerous sheets with numerous cells with numerous formulas. And it’s error prone. It’s not my sheet and I don’t know all the formula not even, why they are there (lololol).

With my relative minimal vba-knowledge I have not found a solution. Can anyone help me? Is there a possibility within vba to not only change the links file-wise but sheet-wise too without running thru all the sheets and formulas?

Thanks in advance
mhagi

Kev · #2 01-30-2017, 12:22 PM

1) Is this something that will happen again in 12 months?
2) How many worksheet names have changed?
3) What are the old worksheet names? What are their equivalent new names?

mhagi · #3 01-31-2017, 01:45 AM

Hi Kev,

thought, I'd mentioned that already.

Is this something that will happen again in 12 months?

Yes. Probably will happen again next year, and the year after and ..... and.
And I already tried to get the department to neutralize their file names etc.
But they are accountants!! You know, these people who need to have facts written down, spelled, sung, danced and ratified by congress (no insult intended. They pay my check. But Facts are Facts! lololol). They decide on a name, that’s it!

How many worksheet names have changed?

Which worksheets are you asking after? Links need to be changed in 94 files. Each file has about 14 sheets. Most sheets have formulas with linked informations.
Basic files (those the links refer to and I wish to change) are 3 to 5.

3) What are the old worksheet names? What are their equivalent new names

Example:
old name of file: workdays2016 – old sheet Name: 2016
new file-Name: workdays2017 - new sheet Name: 2017

Any suggestions other than modifying each formula?

Thanks in advance

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

mhagi · #5 01-31-2017, 03:20 AM

Hi Kev,

thanks very very much. I appreciate your help but I already know, how to change the links within the formulas.
I was hoping there would be another easier ( and less time consuming) way.

Thanks again

Kev · #6 01-31-2017, 03:38 AM

But it will be fast - it will update all the links in one file in one run of one VBA - can't get faster than that....
....and could then be tailored to loop through all 94 files in one go if we can identify them easily to VBA

But it does depend on how consistent you (or your accountants) have been in creating parallel files

Quote:

I was hoping there would be another easier (and less time consuming) way.

- that is why I was asking about next year - we should be able to create a table of 2017 vs 2018 links and then perhaps be able to change the value in one cell from 2017 to 2018 and do it all painlessly.

If you provide me with the info requested in my previous post , I think you will be pleased with the speed of the whole process

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to 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