Having Trouble deleting a missing reference
 

Hi,

I am relatively new to Excel so any help you could provide would be
greatly appreciated. I have a macro that updates various (approx 550)
that have been sent out to users. There was a problem with the
templates and I need to delete missing references. The code I use to do
this is shown below.

Const ver As Integer = 1

Dim worksheetversion As Integer
Dim currentRef As Object
Dim response As Integer
Dim i As Integer
Dim theRef As Variant

worksheetversion = wb.Worksheets("ENTITY_INFO").Cells(1, 2).value

If Not (worksheetversion < ver) Then Exit Sub

For i = wb.VBProject.References.Count To 1 Step -1
Set theRef = wb.VBProject.References.Item(i)
If theRef.IsBroken = True Then
'On Error Resume Next
wb.VBProject.References.Remove theRef
'On Error GoTo 0
End If
Next i

In the code, wb refers to a different workbook that is open and
unprotected.

In the for loop, when I get to the reference that is missing I get the
following error: 'Object Library not registered'. It loops through all
the references fine, but is erroring out only on the missing
references. Any ideas what might be going wrong?

Having Trouble deleting a missing reference
 

I thought I would also mention that the specific library I am trying to
remove is Microsoft ActiveX Objects 2.8 Library.

Having Trouble deleting a missing reference
 

The Remove method is normally used to remove an item from a collection of
objects. Removing and adding references is normally accomplished by opening
the VBE (Visual Basic Editor) and selecting the Tools menu References and
then either check to add or uncheck to delete the references as appropriate.
You can open the VBE with Alt + F11. I offer this advice since I don't
understand your question.

" wrote:

Hi,

I am relatively new to Excel so any help you could provide would be
greatly appreciated. I have a macro that updates various (approx 550)
that have been sent out to users. There was a problem with the
templates and I need to delete missing references. The code I use to do
this is shown below.

Const ver As Integer = 1

Dim worksheetversion As Integer
Dim currentRef As Object
Dim response As Integer
Dim i As Integer
Dim theRef As Variant

worksheetversion = wb.Worksheets("ENTITY_INFO").Cells(1, 2).value

If Not (worksheetversion < ver) Then Exit Sub

For i = wb.VBProject.References.Count To 1 Step -1
Set theRef = wb.VBProject.References.Item(i)
If theRef.IsBroken = True Then
'On Error Resume Next
wb.VBProject.References.Remove theRef
'On Error GoTo 0
End If
Next i

In the code, wb refers to a different workbook that is open and
unprotected.

In the for loop, when I get to the reference that is missing I get the
following error: 'Object Library not registered'. It loops through all
the references fine, but is erroring out only on the missing
references. Any ideas what might be going wrong?

Having Trouble deleting a missing reference
 

JLGWhiz,

Thanks for the response . Basically a previous employee distributed a
workbook with some macros to various users. There were some changes
that need to be done on those workbooks but while doing the changes I
was running into some trouble and realized there was a missing
reference to the Microsoft ActiveX Data Objects 2.8 library. So, I used
the above code to go through all references in the distributed
workbooks to delete any MISSING references (by missing I mean
references that are marked as missing when I go to Tools-References).

In the loop when I reach anything that is marked as missing, I get the
error that I previously mentioned.

I hope this clarifies the problem.

Regards