How do I remove a reference in VBA?

Question

Microsoft Access 2007

I can add a reference by using,

Application.VBE.ActiveVBProject.References.AddFromFile "C:\filelocation"

However

Application.VBE.ActiveVBProject.References.Remove "C:\filelocation"

Gets the Error of:

"Comile Error"

"Type Mismatch"

Answer 1

Try this one, works everywhere, except you have no access to the VBA project.

Andreas.

Sub Test()

  Dim Ref As Object 'VBIDE.Reference

  With Application.VBE.ActiveVBProject

    For Each Ref In .References

      'With Ref

      '  Debug.Print .Name, .Major & "." & .Minor, .FullPath

      'End With

      If InStr(1, Ref.FullPath, "C:\FileLocation", vbTextCompare) = 1 Then

        .References.Remove Ref

        Exit For

      End If

    Next

  End With

End Sub

Answer 2

Figured it out Hans. I used:

Dim  ref As References

For Each ref in References

     If LCase (red.FullPath) = LCase("C:\FileLocation") Then

          References.Remove ref

          Exit For

     End If

Next Ref   

Thank you!

Answer 3

Thank hans, but no go on the code.  Is what you provided for excel? 

I tried replacing the "ActiveWorkbook" for "Application.VBE.Active" and on both attempts it would error on line

"If LCase(ref.FullPath)..."

I am guessing I am still having troubles locating the references in Visual Basic.

Answer 4

Remove expects a Reference object as argument, not a string. Try this:

    Dim ref As Reference

    For Each ref In ActiveWorkbook.VBProject.References

        If LCase(ref.FullPath) = LCase("C:\FileLocation") Then

            ActiveWorkbook.VBProject.References.Remove ref

            Exit For

        End If

    Next ref