automatically update links to add-ins
 

When I copy an Excel file from my C: drive to a LAN drive, any formulas that
use functions defined in an add-in are changed so that the add-in functions
reference the new location of the Excel file, instead of the path for the
loaded add-in.

Is there any way to automatically update these links, without the user being
prompted to update linked data? Setting Application.AskToUpdateLinks = False
will prevent the prompt, but then it returns a different dialog box that the
file cannot be found.

I know that once the file is opened that I can manually go to ToolsLink to
loaded add-ins, and this will relink all of my formulas at one time.
However, I haven't found how (or if) I can utilize this from VBA.

Thanks.

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

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
review.

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

automatically update links to add-ins
 

One way to get round the update links message is to have a 2nd simple
"loader" workbook which in its Auto_Open just does

' open the real workbook without updating links.
Workbooks.Open ThisWorkbook.Path & "\RealBook.xls", UpdateLinks:=0
' run the Auto_Open for the real workbook
ActiveWorkbook.RunAutoMacros xlAutoOpen
' close me
ThisWorkbook.Close False

The real workbook can do any link munging that may be needed in its
Auto_Open.

Excel 2002 introduced more options on how to handle the startup prompt
for links, as you probably know.

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