|
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()
'Macro purpose: To add a reference to the project using the GUID for the
'reference library
Dim strGUID As String, theRef As Variant, i As Long
'Update the GUID you need below.
strGUID = "{00020905-0000-0000-C000-000000000046}"
'Set to continue in case of error
On Error Resume Next
'Remove any missing references
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
'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear
'Add the reference
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:=strGUID, Major:=1, Minor:=0
'If an error was encountered, inform the user
Select Case Err.Number
Case Is = 32813
'Reference already in use. No action necessary
Case Is = vbNullString
'Reference added without issue
Case Else
'An unknown error was encountered, so alert the user
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
|