Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry for the length but I want to be clear on what I did to this point. I really appreciate any help on this. I have a problem that seems very simple but I do not understand why it doesn’t work.

    I began by wanting to write code that adds a reference to an existing workbook to get rid of the manual process of linking the reference through the Visual Basic editor > Tools > References.

    That was simple:

    Public Sub AddReference()
    ThisWorkbook.VBProject.References.AddFromFile _
    "add file path here.xla"
    End Sub

    I thought unlinking an old reference that has gone “Missing” in the list would be similar and I found the ThisWorkbook.VBProject.References.Remove method. So I tried

    Public Sub RemoveReference()
    ThisWorkbook.VBProject.References.Remove ("NameOfReferenceInList")
    End Sub

    This had a Type Mismatch error. I checked the Object Browser and apparently Remove is part of an index collection (there is no “RemoveFromFile”). I researched the web on this topic and found several examples of removing references using this method. However, any that I run give me another error: “Can’t Find Project or Library”. One example which removes all broken references:

    Sub DeleteReference()
    Dim i As Long
    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
    Set theRef = ThisWorkbook.VBProject.References.Item(i)
    If theRef.IsBroken = True Then
    ThisWorkbook.VBProject.References.Remove theRef
    End If
    Next i
    End Sub

    I did some more research and apparently there may be an “Applications Extensibility” reference I need but I do not understand what it is or how to reference it.

    Can anyone help me remove a reference in my workbook using VBA?

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='newb' post='768970' date='02-Apr-2009 20:07']I did some more research and apparently there may be an "Applications Extensibility" reference I need but I do not understand what it is or how to reference it.[/quote]

    Have you tried?
    [attachment=83198:Extensib.GIF]
    Attached Images Attached Images
    Regards
    Don

  3. #3
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have tried checking this and got the same message. Is there something else that needs to be referenced in the code?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your DeleteReference example should run even without a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library.

    However, if your workbook has missing references, that in itself may be the cause of the failure of the code to run! One of the things you could try is to place the DeleteReference code in a module of its own.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='newb' post='768970' date='02-Apr-2009 20:07']This had a Type Mismatch error. I checked the Object Browser and apparently Remove is part of an index collection (there is no "RemoveFromFile"). I researched the web on this topic and found several examples of removing references using this method. However, any that I run give me another error: "Can't Find Project or Library". One example which removes all broken references:

    Sub DeleteReference()
    Dim i As Long
    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
    Set theRef = ThisWorkbook.VBProject.References.Item(i)
    If theRef.IsBroken = True Then
    ThisWorkbook.VBProject.References.Remove theRef
    End If
    Next i
    End Sub[/quote]
    Try adding

    Dim theRef As Variant
    Regards
    Don

  6. #6
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='769030' date='03-Apr-2009 14:17']Try adding

    Dim theRef As Variant[/quote]


    That worked- thanks!

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='newb' post='769063' date='03-Apr-2009 10:27']That worked- thanks![/quote]

    What version of Office are you running?

    Subsequent to my last post I ran a couple of tests in 2003 and found that the declaration made no difference; as long as the 'Option Explicit' statement was not in the declaration area of the module.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •