Using VBA to Add A Reference

[dlssargent]

I am trying to force a reference in excel using VBA. I am working at a client and for some reason 3 users are able to use the workbook without issue but two others machines appear as if they are not looking at the correct reference.

I tried the following but got "Error 1004 in AddReference":

I tried to adjust the code that I found at Source code:
http://msdn.microsoft.com/archive/de...ce07042002.asp (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarofftalk/html/office07042002.asp)

Thanks for any guidance

'Function called by TestAddReference()
' Purpose: Adds a reference to the target project in the VBE.
' Accepts:
' ProjectName: Target project name.
' ReferencePath: Full path to the target reference.
' Returns:
' True if the reference was successfully added.
Public Function AddReference( _
ByVal ProjectName As String, _
ByVal ReferencePath As String) As Boolean


Dim oVBE As Object
Dim oVP As Object
Dim oRefs As Object
Dim oRef As Object
Dim bRefSet As Boolean
Dim strRef As String

On Error GoTo AddReference_Err

Set oVBE = Application.VBE
Set oRefs = oVBE.VBProjects(ProjectName).References

bRefSet = False

'Check any of the reference
For Each oRef In oRefs
' Can't add reference more than once.
strRef = oRef.FullPath
If oRef.FullPath = ReferencePath Then
bRefSet = True
'oRefs.Remove oRef 'to remove a reference
End If
Next oRef

'Add the link
If bRefSet = False Then
oRefs.AddFromFile ReferencePath 'to add a reference
End If

AddReference_End:
AddReference = True
Exit Function

AddReference_Err:
MsgBox "Error " & Err.Number & " in AddReference: " & Err.Description
AddReference = False

End Function


'Macro to call AddReference()
' Purpose: Tests the AddReference function.
'Make sure of strREF_PATH (What'sBest! add-in location)
'Make sure of strCUR_PROJ (the current project, this file)
Public Sub TestAddReference()

Dim strREF_PATH As String
Dim strCUR_PROJ As String

' strREF_PATH = "C:\Program Files\Microsoft Office\Office10\Library\wba.xla"
strREF_PATH = "X:\Program Files\Microsoft Office\Office11\Library\wba.xla"
strCUR_PROJ = "Optimization Tool"

' Add a reference to the Microsoft Scripting Runtime.
If (AddReference(strCUR_PROJ, strREF_PATH) = True) Then
MsgBox "Reference to '" & strREF_PATH & "' has been added!"
Else
MsgBox "Reference to '" & strREF_PATH & "' could not be added!"
End If

End Sub