Is there a way to programmatically access links and change them in VBA?
Specifically, we're doing a migration and want to be able to change names of
the links in a spreadsheet from .123 to .xls (converting the source files,
then go back to the destination file and change all the extensions).

TIA.

J. Freed wrote:
Is there a way to programmatically access links and change them in VBA?
Specifically, we're doing a migration and want to be able to change names of
the links in a spreadsheet from .123 to .xls (converting the source files,
then go back to the destination file and change all the extensions).


A macro like this should do it:

Sub ChangeLinksFrom123ToXLS()
Dim vLinks
Dim iLink As Integer
vLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(vLinks) Then Exit Sub
For iLink = LBound(vLinks) To UBound(vLinks)
If Right(vLinks(iLink), 3) = "123" Then
ActiveWorkbook.ChangeLink vLinks(iLink), _
Left(vLinks(iLink), Len(vLinks(iLink)) - 3) & "xls", _
xlExcelLinks
End If
Next
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup