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)

ASKER CERTIFIED SOLUTION

Kent Dyer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ondrejovic

ASKER

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