Discussion:
|
Assume that your users share workbooks between Office 97 and Office 2000 (or higher). When the 97 user sends the file on, the workbook opens fine, and the reference libraries are automatically updated, but when they are returned to the 97 user, the reference libraries are listed as "missing". (This happens because they've been upgraded to a newer version that Office 97 does not recognize.) This code will remove any missing references, and then add a reference on the fly, based on the GUID you specify. (The GUID provided below will add a reference to the Microsoft Word library, and is NOT VERSION SPECIFIC!)
NOTE: In Office 2002 or later, the TRUST ACCESS TO VISUAL BASIC PROJECT, in the Macro security settings, MUST be checked, or the code will not work.
For a KB Entry to get the GUID for the library you need, see http://www.vbaexpress.com/kb/submitcode.php?kb_id=278.
|
Sub AddReference()
Dim strGUID As String, theRef As Variant, i As Long
strGUID = "{00020905-0000-0000-C000-000000000046}"
On Error Resume Next
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
Err.Clear
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUID, Major:=1, Minor:=0
Select Case Err.Number
Case Is = 32813
Case Is = vbNullString
Case Else
MsgBox "A problem was encountered trying to" & vbNewLine _
& "add or remove a reference in this file" & vbNewLine & "Please check the " _
& "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
End Select
On Error GoTo 0
End Sub
|