Hi Dave,
Is there a way to automate it or do
I have to tell anyone who I share the addin with that they may need to do
this?
Assuming the addin will be installed, it could contain a class module that
hooks application events and detects when a workbook is opened. It could
then check if the workbook contains any links to the addin, and if so change
them to itself:
In a standard module:
'Create an instance of our event-handling class
Dim mclsAppEvents As CAppEvents
Sub Auto_Open()
Set mclsAppEvents = New CAppEvents
End Sub
'A public function to test it with
Public Function AddTwo(d1 As Double, d2 As Double)
AddTwo = d1 + d2
End Function
In a class module called CAppEvents:
Dim WithEvents moXL As Application
Private Sub Class_Initialize()
Set moXL = Application
End Sub
Private Sub moXL_WorkbookOpen(ByVal Wb As Workbook)
Dim vLink As Variant
'Scan through the Excel links in the workbook
For Each vLink In Wb.LinkSources(xlLinkTypeExcelLinks)
'Does it link to this addin name?
If InStr(1, vLink, "\" & ThisWorkbook.Name, vbTextCompare) 0 Then
'Yes, so update it to point to this file
Wb.ChangeLink vLink, ThisWorkbook.FullName, xlLinkTypeExcelLinks
Exit For
End If
Next
End Sub
In this case, they'll still get an 'Update Links' prompt, but if they click
'Don't update', the code in the addin will update the links for them.
Regards
Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk