Using Solver from Access

Ondrejovic
Ondrejovic used Ask the Experts™
on
I want to call Solver on excel-file from  Access-VBA.
(I have created excel-file with data  and I would like to finalize it with Solver Addon.)

How should I do it?
(I didn't find any help on Internet.)

(I have Access 2007)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Security Analyst Senior
Commented:
Hmm..  Interesting..  This came pretty quick from http://utteraccess.com

Run Excel Solver from MsAccess

HTH,

Kent

Author

Commented:
thanx :)

Correct example is attached.
Sub Solver()

'set reference to excel object library
'AddIns("solver add-in").Installed = True
Dim objXL As Excel.Application
Dim strText As String
Dim blnCheck As Boolean
Set objXL = CreateObject("Excel.Application")
strText = objXL.Application.LibraryPath

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

With objXL
    .Visible = True
    .Application.DisplayAlerts = False
    .Workbooks.Open (strText & "\solver\solver.xlam")
    .Application.DisplayAlerts = True
    .Workbooks("SOLVER.XLAM").RunAutoMacros 1
    .Application.ScreenUpdating = True
    
    Set wb = .Workbooks.Add
    Set ws = .Sheets.Add
    ws.Name = "Calculations"
    ws.Range("A1:A16") = 1
    ws.Range("A19").Formula = "=sum(A1:A16)"
    .Sheets("Calculations").Activate
    
    .Run "SOLVER.XLAM!SolverOk", ws.Range("$A$19"), 3, 50, ws.Range("$A$16")
    .Run "SOLVER.XLAM!SolverSolve", True
    .Run "SOLVER.XLAM!SolverFinish", KEEP
    
    .Application.ScreenUpdating = True
    .UserControl = True
    .Visible = True
End With
        
    objXL.Quit
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial