Solver reference problem

[Martien Janssen]

I am using a workbook with VBA code referring to the Excel solver. When I
transfer the workbook to a different PC, I may get problems as the reference
may get broken due to a different location of the solver.xla file. Is there
a way to handle this without involving the user of the workbook ? I have
added the following code:

solverpath = Application.Librarypath & "\solver\solver.xla"
ThisWorkbook.VBProject.References.AddFromFile solverpath

This gives a problem since the solver is already referenced initially.
Somehow, I have to dereference the solver first and then follow the above
instructions, but I don't know how. I tried with
ThisWorkbook.VBProject.References.Remove (xxx) but do not know how to code
xxx ?

Any ideas,

Martien
PS I am using Excel 97.

 

[Tom Ogilvy]

Recently posted by Dana DeLouis

You may prefer this other version that I use. HTH. :>)

Sub SolverInstall()
'// Dana DeLouis
Dim wb As Workbook

On Error Resume Next
' Set a Reference to the workbook that will hold Solver
Set wb = ActiveWorkbook

With wb.VBProject.References
.Remove .Item("SOLVER")
End With

With AddIns("Solver Add-In")
.Installed = False
.Installed = True
wb.VBProject.References.AddFromFile .FullName
End With
End Sub

 

[Martien Janssen]

Dear Tom (and Dana)

Many thanks, I have had this problem for a long time now in different
applications and this solution seems to work well,

Thanks again,
Martien