CODEWITHSUNDEEP
vbaexcel
Michal
Michal

Reputation: 19

How to remove missing references via script in Excel VBA?

I have excel that is use by many users. Some of them do not have installed all software's and therefore missing references. I am, trying to have script that remove all references that can not be found on C: drive.

I have this code in /Microsoft Excel Objects / ThisWorkbook but is not fully working. Anyone could help me with this. 

Sub TestRef()
Dim REF As VBIDE.Reference
Dim WB As Workbook
Set WB = ThisWorkbook
For Each REF In WB.VBProject.References
    If StrComp(Left(REF.FullPath, 1), "C", vbTextCompare) <> 0 Then
        DelRef
        Debug.Print REF.Name, REF.Description, REF.FullPath
    End If
Next REF
End Sub

Sub DelRef()
Dim REF As VBIDE.Reference
Dim WB As Workbook
Set WB = ThisWorkbook
With ThisWorkbook.VBProject.References
    .Remove .Item("MSForms")
End With    
End Sub

Missing Referece Image

Upvotes: 1

Views: 7566

Answers (2)

Noam Brand
Noam Brand

Reputation: 346

Removing a missing reference will not solve the problem because the reference is needed for some propose or it wouldn't have been added in the first place.

Another way of dealing with missing reference: Closing the Excel file if it has a missing reference, then complete the Object library installation of the needed reference by Excel VBA in a different Excel file.

You may notify the user there is a missing part of the installation and suggest closing the file to complete the installation.

See: Catch 22 -Installing reference library (Selenium) by Excel VBA

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33662

Not with your variables names, but something I wrote a while back and I've been using for a while to remove "Missing" references :

Dim theRef As Variant, i As Long

' loop through all References in VB Project
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
    Set theRef = ThisWorkbook.VBProject.References.Item(i)

    ' if reference is "Missing" >> remove it to avoid error message
    If theRef.isbroken = True Then
        ThisWorkbook.VBProject.References.Remove theRef
    End If

    ' just for Debug
    ' Debug.Print theRef.Description & ";" & theRef.FullPath & ";" & theRef.isbroken & vbCr
Next i

Upvotes: 1

Related Questions