Link to home
Start Free TrialLog in
Avatar of Ondrejovic
OndrejovicFlag for Slovakia

asked on

Using Solver from Access

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)
Avatar of Kent Dyer
Kent Dyer
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ondrejovic


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)"
    .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
End Sub

Open in new window