Hello

I am trying to use Solver through an Excel object in VB6. I'm not sure how
to access Solver through an object objExcel after I've declared objExcel as
Excel.Application. Can anyone provide the syntax for doing this?

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
procedu

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/
_______

jason77 wrote:

Hello

I am trying to use Solver through an Excel object in VB6. I'm not sure how
to access Solver through an object objExcel after I've declared objExcel as
Excel.Application. Can anyone provide the syntax for doing this?

Jason -

If you're using Excel only to get at Solver, you might want to check out Frontline
Systems' other Solver products, which can be used outside of Excel, i.e., directly
from VB. It's likely to be more straightforward to use, especially since you'd be
distributing it, not relying on what random installations your users may have.

http://www.solver.com/

(I have no vested interest in making this suggestion, and I've never used anything
beyond the standard Excel Solver.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Jon Peltier wrote:

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 procedu

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/
_______

jason77 wrote:

Hello

I am trying to use Solver through an Excel object in VB6. I'm not
sure how to access Solver through an object objExcel after I've
declared objExcel as Excel.Application. Can anyone provide the syntax
for doing this?