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

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