Solved

Excel Solver:  Having Trouble invoking from Access 2003

Posted on 2006-07-06
7
307 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

734 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