Deleting broken references

[Ron Dahl]

I am using the following code to delete broken references:

Dim myRef As Object

For Each myRef In ThisWorkbook.VBProject.References
If myRef.IsBroken Then ThisWorkbook.VBProject.References.Remove myRef
Next myRef

I do have a reference set to Microsoft Visual Basic for Applications
Extensibility 5.3, but whenever it gets to a broken reference, I still get
an error message and the reference is not deleted.

The error number is -2147319779 and the err.description was that the Object
Library was not registered.

A little more background is that the the broken reference was set to
Microsoft ActiveX Data Objects 2.7 Library by other users. When a user does
not have Version 2.7, I wanted this reference to be deleted and Version 2.5
(which all of our users seem to have) to be added.

Thanks in advance for any help.

Ron Dahl

 

[David McRitchie]

Hi Ron,
I don't have any comment on your code but have you
checked out Bill Manville's FindLinks program.
http://www.bmsltd.ie/MVP/MVPPage.asp
He is working on a new version and hangs out in
microsoft.public.excel.links

 

[Dave Peterson]

I think I'd dump the references and use late binding.

I don't speak ADO, but maybe something like:


Dim ADOObject As Object

Set ADOObject = Nothing
On Error Resume Next
Set ADOObject = CreateObject("ADODB.Connection")
On Error GoTo 0

If ADOObject Is Nothing Then
MsgBox "not available!"
Exit Sub '???
End If

Could avoid the problem.