ChangeLink with Worksheet Named Ranges as Arguments
 

I saw on an earlier post that the way to update links is to use the
ChangeLink method
I used VBA help and derived the following.

Sub MSLinks2()
' This Works
ActiveWorkbook.ChangeLink _
"\\manches-user1\Common\FINANCE\Forecasting\FY2007\0307\OpsPl an\0307JudgementPaperLinksv001.xls", _
"\\manches-user1\Common\FINANCE\Forecasting\FY2006\1106\OpsPl an\1106JudgementPaperLinksv003.xls", xlExcelLinks
End Sub

As noted in the comment this worked. I was emboldened to try to get the
method to accept strings contained in named ranges
TextForOldLink:=
"\\manches-user1\Common\FINANCE\Forecasting\FY2007\0307\OpsPl an\0307JudgementPaperLinksv001.xls"

This is obtained from:
=E21&MID(Sheet2!$B$3,FIND("\",Sheet2!$B$3,20),FIND ("\",Sheet2!$B$3,56)-FIND("\",Sheet2!$B$3,20)+1)&MID(Sheet2!$B$3,FIND("[",Sheet2!$B$3,1)+1,FIND("]",Sheet2!$B$3,1)-FIND("[",Sheet2!$B$3,1)-1)

TextForNewLink:=
€œ\\manches-user1\Common\FINANCE\Forecasting\FY2006\1106\OpsPl an\1106JudgementPaperLinksv003.xls€�

This is obtained from
=CONCATENATE($E$23,$C$5,$C$7,"\",$D$9&$D$7,"\OpsPl an\",$D$9&$D$7,"JudgementPaperLinks"&$D$11)

An earlier respondent (Jay) suggested that the following would work

Sub UpdateLink1()
With ActiveWorkbook
.ChangeLink _
.ActiveSheet.Range("TextForOldLink").Value , _
.ActiveSheet.Range("TextForNewLink").Value, xlExcelLinks
End With
End Sub

However I get the error message: 'Compile error: argument not optional.

Jay has unfortunately stopped monitoring the thread and I have been unable
to proceed further.

Essentially I just want to pass the contents of a named range (a string) as
an argument of the ChangeLink Method. I would be grateful if someone could
check the syntax and offer advice on where to look next.

Regards

Phil Smith