Solved

Excel Solver:  Having Trouble invoking from Access 2003

Posted on 2006-07-06
7
288 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Launch app from Access custom ribbon 8 33
Create a EXCEL FIle from Access Procedure 1 14
Question about DB Schema 27 53
SetFocus doesn't wait for input 14 32
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

776 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