dastrw
asked on
Excel Solver: Having Trouble invoking from Access 2003
I am trying to run Solver from an Excel worksheet embedded in an Access form.
Below is the ON CLICK code for the button that runs a simple Solver operation. It runs without error, but also without doing anything. The msgbox() should return '10', but instead returns '0' which is the initial value.
What am I doing wrong? Or is what I am attempting impossible?
------------------------CO DE-------- ---------- ---------- -------
Private Sub cmdSolver_Click()
Dim excelApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rangeSet As Range
Dim rangeChg As Range
Set wb = Me.excelWS.Object
Set excelApp = wb.Application
Set ws = wb.Worksheets(1)
Set rangeSet = ws.Range("A1")
Set rangeChg = ws.Range("B1")
rangeSet.Formula = "=A2-B1"
ws.Range("A2").Value = 10
ws.Range("B1").Value = 0
'Add Reference to Solver
On Error Resume Next
Select Case Val(excelApp.Version)
Case 9
wb.VBProject.References.Ad dFromFile ("C:\Program Files\Microsoft Office\Office\Library\Solv er\SOLVER. XLA")
Case 11
wb.VBProject.References.Ad dFromFile ("C:\Program Files\Microsoft Office\OFFICE11\Library\SO LVER\SOLVE R.XLA")
End Select
excelApp.Run "Solver.xla!MenuUpdate"
excelApp.Run "Solver.xla!Auto_Open"
excelApp.Run "Solver.xla!SolverOK", rangeSet, 3, 0, rangeChg
excelApp.Run "Solver.xla!SolverSolve", True
MsgBox (rangeChg.Value)
End Sub
Below is the ON CLICK code for the button that runs a simple Solver operation. It runs without error, but also without doing anything. The msgbox() should return '10', but instead returns '0' which is the initial value.
What am I doing wrong? Or is what I am attempting impossible?
------------------------CO
Private Sub cmdSolver_Click()
Dim excelApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rangeSet As Range
Dim rangeChg As Range
Set wb = Me.excelWS.Object
Set excelApp = wb.Application
Set ws = wb.Worksheets(1)
Set rangeSet = ws.Range("A1")
Set rangeChg = ws.Range("B1")
rangeSet.Formula = "=A2-B1"
ws.Range("A2").Value = 10
ws.Range("B1").Value = 0
'Add Reference to Solver
On Error Resume Next
Select Case Val(excelApp.Version)
Case 9
wb.VBProject.References.Ad
Case 11
wb.VBProject.References.Ad
End Select
excelApp.Run "Solver.xla!MenuUpdate"
excelApp.Run "Solver.xla!Auto_Open"
excelApp.Run "Solver.xla!SolverOK", rangeSet, 3, 0, rangeChg
excelApp.Run "Solver.xla!SolverSolve", True
MsgBox (rangeChg.Value)
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found the workaround for the error I got. I checked a security box to allow access and I have trimmed down the code. However, now I get a "run out of resources" error as if the equation I am asking to solve is too complex.
Here is the trimmed down code:
Private Sub cmdSolver_Click()
Dim excelApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rangeSet As Range
Dim rangeChg As Range
Set wb = Me.excelWS.Object
Set excelApp = wb.Application
Set ws = wb.Worksheets(1)
Set rangeSet = ws.Range("A1")
Set rangeChg = ws.Range("B1")
rangeSet.Formula = "=A2-B1"
ws.Range("A2").Value = 10
rangeChg.Value = 0
ws.Range("A3") = ""
wb.VBProject.References.Ad dFromFile ("C:\Program Files\Microsoft Office\OFFICE11\Library\SO LVER\SOLVE R.XLA")
excelApp.Run "Solver.xla!SolverOK", "$A$1", 3, 0, "$B$1"
excelApp.Run "Solver.xla!SolverSolve", True
MsgBox ("Chg: " & rangeChg.Value & ", Set: " & rangeSet.Value)
ws.Range("A3") = 123
End Sub
Here is the trimmed down code:
Private Sub cmdSolver_Click()
Dim excelApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rangeSet As Range
Dim rangeChg As Range
Set wb = Me.excelWS.Object
Set excelApp = wb.Application
Set ws = wb.Worksheets(1)
Set rangeSet = ws.Range("A1")
Set rangeChg = ws.Range("B1")
rangeSet.Formula = "=A2-B1"
ws.Range("A2").Value = 10
rangeChg.Value = 0
ws.Range("A3") = ""
wb.VBProject.References.Ad
excelApp.Run "Solver.xla!SolverOK", "$A$1", 3, 0, "$B$1"
excelApp.Run "Solver.xla!SolverSolve", True
MsgBox ("Chg: " & rangeChg.Value & ", Set: " & rangeSet.Value)
ws.Range("A3") = 123
End Sub
well we got further
on error resume next
just ignores your errors, that explains why u get nothing
Have u tried this operation in Excel as a standalone test?
Trying this will help u track down any problems
on error resume next
just ignores your errors, that explains why u get nothing
Have u tried this operation in Excel as a standalone test?
Trying this will help u track down any problems
ASKER
It works without problems in Excel.
Now it is trying to run Solver and I get two errors in succession:
Object _Global failed
Object _Application failed
Do you know where I can find the exact syntax to the SolverOK and SolverSolve commands? Maybe I'm passing the arguments in incorrectly.
Now it is trying to run Solver and I get two errors in succession:
Object _Global failed
Object _Application failed
Do you know where I can find the exact syntax to the SolverOK and SolverSolve commands? Maybe I'm passing the arguments in incorrectly.
ASKER
These final errors were a result of me trying to pass Range objects rather than Strings as parameters to the function. So everything is solved now.
Thanks for your help!
Thanks for your help!
oh cool
well done
and thanks for the grade
well done
and thanks for the grade
ASKER
1004: Programmatic access to Visual Basic Project is not trusted.
Then if I go into Debug and try to continue I get a "Method VBProject Failed" error.
Is there a better way to add the reference to solver? Do I need to somehow lower the security level of my embedded Excel form.
And yes I do have an embedded Excel object that I reference near the top as 'wb'.