Problem with Updating links (external references)

[Igor R.]

Hello!
In the follow code I'm getting the error:
Run-time error '1004':
Method 'ChangeLink' of object '_Workbook' failed.

The code of Sub:

Sub UpdateLink(wnd As Workbook, rng As Range, Newlink As String)

    Dim s As String

    If rng.HasFormula And _
       InStr(1, LCase(rng.Formula), "!") <> 0 _
    Then
        s = rng.Formula
        wnd.ChangeLink Name:=s, Newname:=Newlink, Type:=xlExcelLinks
    Else
        rng.FormulaR1C1 = Newlink
    End If

End Sub

I use it in the main Sub as this:

        For i = 1 To NumTask
            SubTask = "M" & i
            Prm = "Run_Prm_M" & i
            If i = 2 Then
                Frm = "='" & thisws.Range("M0_Folder") & ThisWorkbook.Name & "'!Run_Param_" & SubTask
            Else
                Frm = "='" & thisws.Range("M0_Folder") & ThisWorkbook.Name & "'!Run_Param_" & SubTask & "/100"
            End If
            Call UpdateLink(srcwb, srcws.Range(Prm), Frm)
           Next i

Please, help me to figure out where I am doing wrong?

 

[BobBridges]

I've never used ChangeLink, so I'm hardly an expert. But the example in the documentation says it looks like this:

ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _ 
 "c:\excel\book2.xls", xlExcelLinks

This implies to me that it's looking in the formula and changing any instance of one external filename to another filename. Your example seems to be supplying the whole formula for the old filename, rather than just the filename itself.

 

[Igor R.]

I've never used ChangeLink, so I'm hardly an expert. But the example in the documentation says it looks like this:

ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _ 
 "c:\excel\book2.xls", xlExcelLinks

This implies to me that it's looking in the formula and changing any instance of one external filename to another filename. Your example seems to be supplying the whole formula for the old filename, rather than just the filename itself.

Thank you!
As usual, the solution was so simple and close.
Now it works perfect.