View Single Post
  #3   Report Post  
Old August 27th 09, 03:42 PM posted to microsoft.public.excel.links
BigJimmer BigJimmer is offline
external usenet poster
First recorded activity by ExcelBanter: Aug 2009
Posts: 37
Default automatically update links to add-ins

I am (unfortunately) using Excel 2000.

I have code very similar to what you provided (I had found it from a
newsgroup post from 2006) that I had already modified that basically does
what you suggest.

The only thing I didn't like, which is why I asked about this, is that the
user is prompted about updating the links before the logic is run (even when
in the Workbook_Open event).

The problem is that the users I deal with sometimes do things thaey
shouldn't, and so could have unintentionally added links to other workbooks
that they need to fix. I typically instruct them that if they ever get the
"update links" question in a file from me, that they have likely done
something wrong to the workbook, and need to fix the links, which I also tell
them how to do. Since this method still produces that messsage, and doesn't
mean that there are any links that still require review, I was hoping to
avoid the prompt.

I will look further now into adding another layer to my code to determine if
there are any other linked files, other than the add-ins, and provide an
addirtional prompt to the user that there are still links that they need to

Thanks for your prompt reply. I aslo would like to say thank you very much
for Find Links. I have used that for a while now, and it is an extremely
helpful application.

"Bill Manville" wrote:

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"
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
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
End Function

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