Hi!

This question has been asked before and today I am facing the same
problem. Luckily by browsing this forum and using a bit of intuition I
managed to find a solution to this common problem.

If you use the code below you should be able to assign a relative path
to your xla (add-in) file. This will allow you to distribute your
excel files more easily (e.g. by providing your xls files & and
add-ins under one folder). So, here is the code (to be saved in "This
workbook"):

Private Sub Workbook_Open()

Dim response As Integer

On Error Resume Next
If ActiveWorkbook.VBProject.References("<ADDIN NAME").IsBroken _
Then
On Error GoTo Err_handle
ActiveWorkbook.VBProject.References.AddFromFile _
ActiveWorkbook.Path & "\<YOUR ADDIN PATH AND FILENAME"
End If

Exit Sub

Err_handle:
response = MsgBox("Could not Reference ""xxx.xla""" & _
vbCr & """xxx.xla"" must be under directory yyy", _
vbOKOnly + vbExclamation)

End Sub

Description:
Because the code is in "ThisWorkbook" object, it will be run whenever
the workbook is opened.

If the referenced addin is available the "if" statement will do
nothing and the sub will exit. On the other hand, if the addin is not
available, the "if" statement will generate an error. However because
the first "on error" statement is placed just before, it will try to
re-attach the addin by using the "addFromFile" method of the
References collection. In turn, if this fails the error handler will
catch it (thanks to the second "On error" statement) and displays the
error message.

Possible improvements
Include all the addins under a given folder (e.g like winamp behaves
with plugins).

Regards,

Olivier

I have realised that the above code was flawed and will generate a
run-time error if the included add-in was renamed...

Basically, XL won't allow you to add a new reference if the current
project contains missing ones. I have therefore improved that code so
that all missing references are removed, as follows:

Private Sub Workbook_Open()

Dim ref As Variant
Dim addInName As String
Dim relPath As String

addInName = "<YOUR ADDIN NAME"
relPath = "<YOUR RELATIVE PATH"

'Check if the add-in is missing
On Error Resume Next
If ActiveWorkbook.VBProject.references(addInName).isb roken Then

'Removes all missing references
'Notes: this is necessary in order to add new reference
For Each ref In ActiveWorkbook.VBProject.references
If ref.isbroken Then
ActiveWorkbook.VBProject.references.Remove (ref)
End If
Next

'Recreate reference for the add-In
On Error GoTo Err_handle
ActiveWorkbook.VBProject.references.AddFromFile
ActiveWorkbook.Path & relPath & addInName & ".xla"
End If

Exit Sub

Err_handle:
MsgBox prompt:="Could not Reference """ & addInName & ".xla""" &
vbCr & _
"""" & addInName & ".xla"" must be in current
directory", _
Buttons:=vbOKOnly + vbExclamation
End Sub

if would still include a check to ensure i'm not removing (missing)
references to other libraries beside the one i'm checking. Or at least
exclude references to exe,olb and dll..


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(olivier durand) wrote:

I have realised that the above code was flawed and will generate a
run-time error if the included add-in was renamed...

Basically, XL won't allow you to add a new reference if the current
project contains missing ones. I have therefore improved that code so
that all missing references are removed, as follows:

Private Sub Workbook_Open()

Dim ref As Variant
Dim addInName As String
Dim relPath As String

addInName = "<YOUR ADDIN NAME"
relPath = "<YOUR RELATIVE PATH"

'Check if the add-in is missing
On Error Resume Next
If ActiveWorkbook.VBProject.references(addInName).isb roken Then

'Removes all missing references
'Notes: this is necessary in order to add new reference
For Each ref In ActiveWorkbook.VBProject.references
If ref.isbroken Then
ActiveWorkbook.VBProject.references.Remove (ref)
End If
Next

'Recreate reference for the add-In
On Error GoTo Err_handle
ActiveWorkbook.VBProject.references.AddFromFile
ActiveWorkbook.Path & relPath & addInName & ".xla"
End If

Exit Sub

Err_handle:
MsgBox prompt:="Could not Reference """ & addInName & ".xla""" &
vbCr & _
"""" & addInName & ".xla"" must be in current
directory", _
Buttons:=vbOKOnly + vbExclamation
End Sub