editing links

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J. Freed - 16 Mar 2005 17:33 GMT

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.

Reply to this Message

Bill Manville - 16 Mar 2005 18:43 GMT

> 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

Reply to this Message