Re: References to XLA files

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: keepITcool (xrrcvgpbby_at_puryyb.ay)
Date: 07/14/04 

Date: Wed, 14 Jul 2004 05:45:09 -0700

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 >

durandolivier@yahoo.co.uk (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).isbroken 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
>

Relevant Pages