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 Tools>Link 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.

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)<>stSourceName 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

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.

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