Solved

Excel Solver:  Having Trouble invoking from Access 2003

Posted on 2006-07-06
7
298 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

828 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