Hi Jason -
If you open Excel through automation (CreateObject), no addins are started in that
instance. You need to start any addins through code.
Solver should be located within objExcel.ApplicationPath, which returns something
like this: C:\Program Files\Microsoft Office\Office\LIBRARY. It's in the
subdirectory Solver:
C:\Program Files\Microsoft Office\Office\LIBRARY\Solver\Solver.xla
You don't need to open this as an add-in, just use objExcel.Workbooks.Open to open it.
The official way to run Solver through automation is to set a reference to it and
run its VBA commands, but that path is littered with the bones of unsuccessful VBA
programmers, different versions of Excel and Solver having fought for the marrow.
The more reliable way to use solver is to use Excel's Application.Run command, with
the Solver command in quotes. The first thing you must do is run Solver's Auto_Open
procedure:
objExcel.Run "solver.xla!SOLVER.Solver2.Auto_open"
This makes sure that Solver is properly initialized. (Having used Workbooks.Open to
open Solver may have made this step redundant, but I haven't tested this in VB.)
The rest of Solver is automated much the same way. Record some macros in Excel to
get most of the way there, then convert to App.Run syntax. You'll have to make sure
not to skip arguments because the error messages are not very enlightening.
Here is a sampling of commands, roughly in order of how you might invoke them. Many
of the items in quotes are named cells in Excel; you could use the qualified range
addresses instead.
' Reset Solver
objExcel.Run "solver.xla!SolverReset"
' Add Constraints
objExcel.Run "solver.xla!SolverAdd", "Agg1", 3, 0
objExcel.Run "solver.xla!SolverAdd", "Vol1", 2, "Target1"
' Press the "OK" Button
objExcel.Run "Solver.xla!SolverOK", "VolumeMixTotal", 2, _
"TargetVolume", "Cell1,Cell2,Cell3"
' Get the solution
' Result is returned to indicate the relative success of the analysis
Result = objExcel.Run("solver.xla!SolvSolve", True)
If Result <= 3 Then
' Result = 0, Solution found, optimality and constraints satisfied
' Result = 1, Converged, constraints satisfied
' Result = 2, Cannot improve, constraints satisfied
' Result = 3, Stopped at maximum iterations
MsgBox "Solution Found", vbInformation
Else
' Result = 4, Solver did not converge
' Result = 5, No feasible solution
Beep
MsgBox NoSolution, vbExclamation
End If
You'll have to play around quite a bit to make sure it works, record several sets of
models to get the parameters and syntax, and I've left out some of the error traps
and so forth.
This approach worked on a wide variety of combinations of machine, Windows version,
and Office version. The approach with references being set would work on one
machine, then not on any others. It would be fixed for another machine, then not
work on the original one. My partner and I pulled out a lot of hair until we came
across Application.Run.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
