VBA Add/Remove References

[Dustin Ventin]

I am creating a Microsoft Access application that links to Microsoft Project,
and uses VBA coding to transfer data back and forth. Because of this, my
application needs the Reference installed for Microsoft Project.

However, not all of my customers will have Project installed on thier
computers- which means when the system tries to install the reference to
Project, it comes up empty- and can cause many errors. What I want to do is
write code that checks if the reference already exists, and if it is broken
if it does exit. If it is broken, I want it to delete the reference. I use
the code:

References.Remove (referencename)

No errors come up when this portion of the code runs, and no references are
deleted. The application is not affected in the least. Why doesn't the
Remove references command not actually remove references?

Any help would be most appreciated.

Dustin Ventin

 

[Douglas J. Steele]

If the reference is broken, you can't refer to it by name.

What you can do is loop through the References collection and delete any
that are broken:

Sub RemoveBrokenReferences()
Dim refCurr As Reference
Dim intLoop As Integer

For intLoop = References.Count To 1 Step -1
Set refCurr = References(intLoop)
If refCurr.IsBroken = True Then
References.Remove refCurr
End If
Next intLoop

End Sub

(Note that it's necessary to loop through the references backwards from the
last one, because if you delete a particular reference, the order of the
remaining ones changes)

What's probably better in your case, though, is to use Late Binding, so that
you don't actually need to set a reference in the first place. You can
attempt to instantiate a Project object, and trap for the error that will
occur if the user doesn't have Project installed. Tony Toews has an
introduction to Late Binding at
http://www.granite.ab.ca/access/latebinding.htm