Delete references

[Gareth]

I have the following problem:

Work has Excel '97, users have been taking work home and opening the file on
a newer version of Excel. When they bring the file back to work the
refereneces box displays the following:

MISSING: Microsoft Outlook 9.0 Object Library
MISSING: Microsoft Visual Basic for Applications Extensibility 5.3

I need to be able to add another menu item to my custom menu which will
allow the user to 'untick' these references and add the relevant '97 ones
(Microsoft Outlook 98 Object Model and Microsoft Visual Basic for
Applications Extensibility).

The following seems to work to add the required references, but only after
the MISSING ones have been deleted.

Sub addreference()
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program
Files\Common Files\Microsoft Shared\Vba\Vbeext1.olb"
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program
Files\Microsoft Office\Office\msoutl85.olb"
End Sub


Is it possible to delete the MISSING references using code?

The project is protected, does it need to be unprotected to do this??

Thanks in advance.

 

[Jim Rech]

You might try something like this to remove broken references:

Sub a()
Dim Counter As Integer
With ThisWorkbook.VBProject.References
For Counter = 1 To .Count
If .Item(Counter).IsBroken Then
.Remove .Item(Counter)
End If
Next
End With
End Sub