[AccessD] Run Excel Solver from Access

I found this that might br of some use. HTH
Jim Hale


Using Solver With Visual Basic

Frontline developed Solver.  Apparently Solver defines names For each
constraint, then hides the name definitions.  After un-hiding them, you will
find that the references were to cells like $AC$46, $AD$46, etc..
Apparently, in the named definitions it had the correct cells identified,
but in the actual Solver dialog box it will not show cells beyond the "Z"
column.  Frontline's web site is:

http://www.frontsys.com

You can also find a number of internet articles by Microsoft at:

http://support.microsoft.com/support/Excel/Content/Solver/SOLVER.asp 

They have a number of suggestions on it to help you in using Solver.

	Here are the steps to  display the hidden names and "restart" Solver
fresh:

1.  Use "Save Model" in the options dialog to save the model onto the sheet.
(This will save the embedded formulas onto the worksheet.)

2.  Use this macro that makes all the hidden defined names available:


Sub unhide_Names()
	Dim na As Name
	For Each na In ActiveWorkbook.Names
	   na.Visible = True
	Next
End Sub

3.  Go to Insert-Name-Define, you will see all the Solver names, starting
with solver_.

4.  Simply delete all of these names.

5.  Start the Solver; the dialog will be blank, but you can use the "Load
Model" option to restore the model you saved before.



Finally, here's another tip Frontline Systems provided:

Frontline does not recommend using formulas in the right hand sides of
constraints. It is allowed, but causes severe performance problems. Instead,
if you have:
$A$1 <= 0.5*$D$5*93

just place the right hand side in a cell, such as $B$10, so that $B$10 has
the formula: =0.5*$D$5*93, and make the constraint:
$A$1 <= $B$10

	You can find some useful information at these Internet locations
regarding using Microsoft Excel Solver with Visual Basic macros:

http://support.microsoft.com/support/excel/content/solver/solver.asp
Creating Visual Basic Macros that Use Microsoft Excel Solver

http://www.frontsys.com/xlhelp.htm
Help for Microsoft Excel Solver Users

-----Original Message-----
From: Kaup, Chester [mailto:Chester_Kaup at kindermorgan.com]
Sent: Monday, May 09, 2005 1:11 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Run Excel Solver from Access


I need to run the solver add-in in Excel from Access. I have some code
but how do I pass values to the function and then bring them back to
access? Below is what I have so far.

Dim Obj As Excel.Application

Set Obj = CreateObject("Excel.Application")

Obj.Workbooks.Open (Obj.Application.LibraryPath & "\SOLVER\solver.xla")

 

Then something like this?

Obj.Application.Run("solver.xla",???????????)

 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.