CODEWITHSUNDEEP
c#excelcomsolvervba
TimY
TimY

Reputation: 5416

Controlling Excel addins (e.g. solver) from COM in C#

I would like to know if it is possible to control Excel's addins, specifically solver from C#.

I have gotten this far:

var Solver = Excel.Application.AddIns["Solver Add-in"];

I can't find any documentation, but when exploring this COM Object in Microsoft's Visual Studio debugger, it shows a name parameter referring to "SOLVER.XLAM", which I presume means I'm on the right track. There don't appear to be any useful methods in this COM Object though.

Does anyone know how to rewrite this VBA code to work in C# for example?

SolverOk SetCell:="$G$39", MaxMinVal:=2, ValueOf:=0, ByChange:= _
    "$H$5:$H$17,$H$19:$H$22,$H$24:$H$32,$H$34:$H$37", Engine:=1, EngineDesc:= _
    "GRG Nonlinear"
SolverSolve

Upvotes: 3

Views: 1884

Answers (1)

Snorex
Snorex

Reputation: 914

You could write a wrapper for the SolverOK function in VBA, as a subroutine in your workbook's macro code. Next you would call that from C#.

Here's an example of barebones VBA:

Sub SolverOkWrapper(setCell As String, maxMinVal As Double, valueOf As Double, byChange As String, engine As Integer, engineDesc As String)

    SolverOk setCell, maxMinVal, valueOf, byChange, engine, engineDesc
    SolverSolve True

End Sub

Then in C# you can do something like this:

        Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
        appExcel.Visible = true;
        //If you go with this route, use appExcel.AddIns["Solver Add-In"].Installed() to check if solver is installed before continue

        Microsoft.Office.Interop.Excel.Workbook workBook = appExcel.Workbooks.Open("C:\\yourPathHere\\yourWorkbookWithWrapperMacroHere.xlsm",
    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing);
        appExcel.Run("SolverOkWrapper", "$A1$1", 1, 0, "$B$1", 1, "GRG Nonlinear");

Upvotes: 3

Related Questions