How to run Solver from Visual Basic?

[Dinosaur51]

I have an Access database application that creates an excel application, an
excel spreadsheet and formats all the data in the appropriate rows and
columns of a worksheet. I would like to launch Solver from the new excel
application, but I cannot run it. I have loaded solver in my references, and
if I look through the addins of the newly created spreadsheet, it's there. Of
course, Access itself does not recognize the solver addin.
Any ideas (including how to generate excel modules from an access program,
then execute the resulting module)? Alternatively, is there a similar addin
for Access (sorry that this is not the forum for that)?
Thanks

 

[Tom Ogilvy]

If you are opening excel from access using automation, then addins are not
loaded automatically to speed things up.

Here is some sample Excel code to add the solver addin and crate a reference
to it:
(written by Dana DeLouise)

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

http://support.microsoft.com/default.aspx?scid=kb;en-us;843304
How to create Visual Basic macros by using Excel Solver in Excel 97

 

[Dinosaur51]

I tried this from Access 2003 and got the following problems:
It would not let me access VBProject saying "Programmatic access to
VBProject not trusted."
the statement wb.VBProject.References.Remove.Item("SOLVER") came back
highlighting .Remove and saying "Argument not optional"
the statement app.wb.application solverOK (etc) returns a Function or
subfunction not defined.


I don't seem able to get beyond the compiler (interpreter, or whatever).
Thanks for your help.

 

[Tom Ogilvy]

Accest to the Project is a security setting made by the user. That can't be
overridden with code. YOu can change that in Excel in tools=>Macros=>Security

Trusted Publishers Tab,
at the bottom left, select the two checkboxes.


the code I posted looked like this:

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

Note that there is a space between .Remove and .Item

 

[Dinosaur51]

Oh, the joys of proportional fonts! Thanks for the clarification. In Access,
one of the check boxes (the lower one) is "greyed out" and cannot be
selected. In Excel, I did it, but it does not save the setting and must be
set everytime by the user, in the newly created spreadsheet before the
statement is executed. I suppose being able to change that "programatically",
as they say, would be a breach of security! Once it is selected, it comes
back that "SOLVER" is out of range.
This seemed like such a good idea, too!

Do you know of any REASONABLY priced optimization packages (something less
than the $1500 that Solver.com wants), or a way to hack into solver.dll?

So near, yet so far!
Regards,
Mike

 

[Dinosaur51]

Correction to my previous reply. Once the created spreadsheet was closed, now
new spreadsheets are created with that box checked. The other comment of
subscript out of range still applies, though.
Thx
Mike