• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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?

------------------------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
  ws.Range("B1").Value = 0
     
  'Add Reference to Solver
  On Error Resume Next
  Select Case Val(excelApp.Version)
    Case 9
      wb.VBProject.References.AddFromFile ("C:\Program Files\Microsoft Office\Office\Library\Solver\SOLVER.XLA")
    Case 11
      wb.VBProject.References.AddFromFile ("C:\Program Files\Microsoft Office\OFFICE11\Library\SOLVER\SOLVER.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

0
dastrw
Asked:
dastrw
  • 4
  • 3
1 Solution
 
rockiroadsCommented:
get rid of

on error resume next

then run it
lets see where it fails


another thing

add


option explicit


on the top of your module code, then do a debug/compile


do u have a excel object in your form?
0
 
dastrwAuthor Commented:
Thanks for the suggestions.  I get this error when it tries to add the reference to solver:

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'.
0
 
dastrwAuthor Commented:
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.AddFromFile ("C:\Program Files\Microsoft Office\OFFICE11\Library\SOLVER\SOLVER.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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
rockiroadsCommented:
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

0
 
dastrwAuthor Commented:
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.
0
 
dastrwAuthor Commented:
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!
0
 
rockiroadsCommented:
oh cool
well done
and thanks for the grade
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now