automatically update links to add-ins
I am not familiar with the "Link to loaded add-ins" option.
Which version of Excel are you using?
If I were faced with this I guess I would do something like the
following (untested) macro
Sub FixUpAddInLinks()
Dim vLinks
Dim iLink As Integer
Dim stSourceName As String
Dim stFileName As String
vLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(vLinks) Then
For iLink = LBound(vLinks) To UBound(vLinks)
stSourceName = LCase(vLinks(iLink))
If Right(stSourceName,4)=".xla" or Right(stSourceName,5)=".xlam"
Then
stFileName = Mid(stSourceName,InStrRev(stSourceName,"\")+1)
' we have a link to an add-in
If IsIn(Workbooks, stFileName) Then
' an add-in with that name is present
If LCase(Workbooks(stFileName).FullName)<stSourceNam e Then
' but it's from a different place
With ActiveWorkbook
.ChangeLink stSourceName, Workbooks(stFileName).FullName
End With
End If
End If
End If
Next
End If
End Sub
Function IsIn(oCollection As Object, stName As String)
Dim O As Object
On Error GoTo NotIn
Set O = oCollection(stName)
IsIn = True
NotIn:
End Function
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
|