My VBA macro calls Solver.Solve, which requires the code
to reference solver.xla. Unfortunately, the location of
solver.xla is different for Office 2000 (C:\Program
Files\Microsoft Office\Office\Library\Solver) than Office
XP (C:\Program Files\Microsoft Office\Office10
\Library\Solver).

Thus, if I set the reference (using the Visual Studio 6
IDE menuitem "Tools" "References...") for Office XP,
then the macro fails on Office 2000, and visa versa.

How can I create a single version of my macro that
automatically references the proper location of
solver.xla so it can call SolverSolve?

I have tried the following approaches so far (without
success)...

1. Application.VBE.ActiveVBProject.References.AddFrom File
Application.LibraryPath & "\Solver\Solver.xla"

This approach ALMOST works, but it requires the user to
lower their security settings for macros (allowing self-
referential code), which is a major downside in today's
world of macro viruses. Also, it sometimes fails with a
namespace conflict, even though solver.xla is not active.

2. status = Application.Run("SolverSolve", True)
instead of:
status = SolverSolve(UserFinish:=True)

Using Application.Run() doesn't solve the problem... the
call still fails unless the reference is set.

3. Set obj = CreateObject("Excel.Solver")
or: Set obj = CreateObject("Excel.Solve")
or: Set obj = GetObject(,"Excel.Solver")
or: Set obj = GetObject(Application.LibraryPath
& "\Solver\Solver.xla")
etc. etc. etc.

I have not been able to find a proper incantation of
CreateObject() or GetObject() parameters that return an
object in which I can invoke the SolverSolve
function/method.

I even tried a few zanier things to no avail.

Please help! How can I write portable code that calls
SolverSolve and works with BOTH Excel XP and Excel 2000
(without requiring my macro code file to be copied into
the same directory as solver.xla)?

Any assistance is greatly appreciated.
Chris Russell