changing link sources in excel-2010 files
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
|