Thread: Change source of a link in VBA

Hi,

As part of a VBA sequence, I want to change the source of a link in an excel spreadsheet.
In the file there is only one source. I was the macro to change whatever source link is in the file to a given link.

I started like that :

Sub Changelink()

Dim x0 As Variant
x0 = ActiveWorkbook.LinkSources(xlExcelLinks)
ChDir "G:\Example\Budgets"
ActiveWorkbook.ChangeLink Name:=x0, _
NewName:="example.xls", Type:=xlExcelLinks
End Sub

Many thanks for your help.

Dee

Hi DeeCeeL.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

It would be something like

Sub ChangeLinks()
    Dim arrLinks
    Dim i As Long
    arrLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(arrLinks) Then
        On Error Resume Next
        Application.DisplayAlerts = False
        For i = LBound(arrLinks) To UBound(arrLinks)
            ActiveWorkbook.ChangeLink arrLinks(i), "G:\Example\Budgets\example.xls", xlLinkTypeExcelLinks
        Next i
        Application.DisplayAlerts = True
    End If
End Sub

Originally Posted by DeeCeeL

Why do you include the on error resume next statement?

Lazy way of handling an incorrect file path.

Your code covers an array, would there be a simpler way to perform this task, knowing that there will be only one source link in the spreadsheet?

You could change this

        For i = LBound(arrLinks) To UBound(arrLinks)
            ActiveWorkbook.ChangeLink arrLinks(i), "G:\Example\Budgets\example.xls", xlLinkTypeExcelLinks
        Next i

to this

            ActiveWorkbook.ChangeLink arrLinks(1), "G:\Example\Budgets\example.xls", xlLinkTypeExcelLinks

though I don't really see what you gain.