Programmatically Installing and Referencing Solver.xla

[mcongdon]

Hi Guys,
I've been having some major issues with a workbook that I have to distribute throughout my company. It's a basic solver application, but as expected not everyone has solver. I've tried several different methods that I've found, but I can't seem to (a) consistently get the solver.xla add-in to install and (b) set up the VBA references. Most of the end users are not savvy enough to do these things and hopefully they won't have to. The following code is the closest I've come, many thanks to Jon Peltlier for the code.

Function CheckSolver() As Boolean
'' Adjusted for Application.Run() to avoid Reference problems with Solver
'' © 2004 J. Peltier, Peltier Technical Services.

Dim bSolverInstalled As Boolean

If gbDebug Then Debug.Print Now, "NewCheckSolver"

'' Assume true unless otherwise
CheckSolver = True

On Error Resume Next
' check whether Solver is installed
bSolverInstalled = Application.AddIns("Solver Add-In").Installed
Err.Clear

If bSolverInstalled Then
' uninstall temporarily
Application.AddIns("Solver Add-In").Installed = False

' check whether Solver is installed (should be false)
bSolverInstalled = Application.AddIns("Solver Add-In").Installed
End If

If Not bSolverInstalled Then
' (re)install Solver
Application.AddIns("Solver Add-In").Installed = True

' check whether Solver is installed (should be true)
bSolverInstalled = Application.AddIns("Solver Add-In").Installed
End If

If Not bSolverInstalled Then
MsgBox "Solver not found. This workbook will not WORK.", vbCritical, sTITLE
CheckSolver = False
End If

If CheckSolver Then
' initialize Solver
Application.Run "solver.xla!SOLVER.Solver2.Auto_open"
End If

On Error GoTo 0
End Function

When I reference this module in the Workbook_Open event, I get an error that says " 'h:\solver.xla' cannot be found. Check your spelling, or try a different path." I'm fairly new at the whole coding thing, and I don't see any lines that directly reference the h:\ path, so I'm not sure what to do about this aspect.

Using the Application.Run method that he also describes to use solver commands in VBA eliminates the need for installing the reference library for each user, so this has been fixed.

Any help with the error I'm getting would be greatly appreciated.

Thanks!

 

[cheerio]

What value does the machine have for application.LibraryPath

This defines where Excel will look for addins.