Solved

Excel Solver:  Having Trouble invoking from Access 2003

Posted on 2006-07-06
7
315 Views
Last Modified: 2012-06-21
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
Comment
Question by:dastrw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17052027
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
 
LVL 2

Author Comment

by:dastrw
ID: 17052141
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
 
LVL 2

Author Comment

by:dastrw
ID: 17052234
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 65

Expert Comment

by:rockiroads
ID: 17052296
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
 
LVL 2

Author Comment

by:dastrw
ID: 17052338
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
 
LVL 2

Author Comment

by:dastrw
ID: 17053688
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17053710
oh cool
well done
and thanks for the grade
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question