Changing Links in Excel with VBA

[Tom Hickey]

Does anyone know how to change links in Excel with VBA and
using variable names ??

I have an application that moves from one server to
several others. I can create the path that is the current
link as

Current_link_name

I can also create the target link to change to as

New_link_name

My problem is in getting VBA to change the links with the
two variable names, without "hard-wiring" the names into
the command.

Any thoughts ?

 

[Tom Ogilvy]

You could do something along the lines of this:

Sub Tester20()
Dim OldPath As String
Dim NewPath As String
Dim sStr As String, sStr1 As String
Dim i As Long
Dim Alinks as Variant
OldPath = "whatever"
NewPath = "whatever"
Alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(Alinks) Then
For i = LBound(Alinks) To UBound(Alinks)
If InStr(1, Alinks(i), OldPath, vbTextCompare) Then
sStr = Alinks(i)
sStr1 = Application.Substitute(sStr, OldPath, NewPath)
ActiveWorkbook.ChangeLink sStr, sStr1, xlLinkTypeExcelLinks
End If
Next
End If
End Sub