Access VBA to remove MISSING References 1

[MisterC]

Hi folks,
I have a database that I developed on my computer using Access 2003. On my computer, it contains a reference to EXCEL 11.0 object library.

BUT... the db is saved on a server and is used by a few other folks with older versions of Excel. And they need to use the EXCEL 9.0 object library. I have to manually remove the "MISSING" reference to Excel 11.0 and add the one for Excel 9.0 on one of their computers.

AND... whenever I modify my code, the reference goes back to EXCEL 11.0.

I'd like to fix the referencing issue with VBA by removing the "MISSING" reference and replacing it with the correct version. The problem is I can't seem to remove a reference that "IsBroken":

Sub delBrokenRefs()
  Dim r As Reference
  For Each r In Application.References
    If r.IsBroken Then
        Debug.Print "Removing " & r.Guid
        Application.References.Remove (r)
    End If
  Next r
End Sub

I get a runtime error 438 "Object doesn't support this property or method"

Any help?

Thanks!

 

[PHV]

And this ?
Sub delBrokenRefs()
Dim r As Long
For r = References.Count To 1 Step -1
If References(r).IsBroken Then
Debug.Print "Removing " & References(r).Guid
References.Remove References(r)
End If
Next r
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MisterC]

Nope, I got it:

Sub delBrokenRefs()
  Dim r As Reference
  For Each r In Application.References
    If r.IsBroken Then
        Debug.Print "Removing " & r.Guid
        References.Remove r
    End If
  Next r
End Sub

...No () around Reference.Remove's "reference" parameter

Thanks for your help!

 

[MisterC]

Crap, I thought I had it.
Now I'm getting an "Object Library Not Registered" Error...

 

[PHV]

Have you tried my suggestion ?

 

[MisterC]

Yes, thanks. It didn't work either. (I got the same error - "Object Library Not Registered")

 

[MisterC]

...which seems like a strange error. I KNOW the object library isn't registered, thats why I'm trying to remove the reference!

 

[MisterC]

Phew... I think I got it!

My startup form had buttons on it to launch Word & Excel (which nobody was using). I deleted those buttons and the problem seems to be fixed.

Early binding issue, maybe...

Thanks!

 

[PHV]

Glad you sorted it out.
Anyway, the safest way to remove multiple items from a collection is to browse the collection from end to top.

 

[MisterC]

Once Again...!
I'm still getting the same error problem ("Object Library Not Referenced") when I try to run the code on a prior version of Access.

I just don't get it.

 

[HughLerwill]

You should be able to avoid these nightmares if you use late binding to your Excel Objects.

Use something like;

Dim ObjXL as Object
Set ObXL = CreateObject("Excel.Application")

rather than the several ways of doing;

Dim ObjXL as Excel.Application
Set ObXL = CreateObject("Excel.Application")

regards Hugh,