Calling Excel's Solver from java using JACOB

Question

Is it possible to use the Solver add-in to excel from java code when using Jacob?

I found this, but I don't know how to take this javascript example and use Jacob instead http://bytes.com/topic/javascript/answers/693863-using-excel-solver-web-page-using-javascript

Erik Eidt · Answer 1 · 2014-03-21T01:29:41.563

If you can record it as a macro in Excel, I think you'll be able to use JACOB to invoke it. I turned on the recorder and got this, so it appears to be recordable (though I don't know anything specific about that solver.)

Sub Macro1()
    Application.Left = 94
    Application.Top = 668.5
    AddIns("Solver Add-in").Installed = True
    SolverOk SetCell:="$A$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$A$1", Engine:=1 _
        , EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$A$1", MaxMinVal:=1, ValueOf:=0, ByChange:="$A$1", Engine:=1 _
        , EngineDesc:="GRG Nonlinear"
    SolverSolve
End Sub

I suspect that SolverOk and SolverSolve are going to be "Run" calls for the Excel object model operations, something like this (lines 340 of TableReader.java of my QuickRDA project); these lines invoke a specific macro in Excel.

public String GetQuickTab ( int row1, int row2, int col1, int col2, int vis ) {
    Variant v = Dispatch.call ( Application.xa, 0x103 /* "Run" */, "QuickRDA.JavaCallBacks.GetQuickTab", this, row1, row2, col1, col2, vis );
    if ( v == null || v.isNull () )
        return null;
    return v.getString ();
}

The string "QuickRDA.JavaCallBacks.GetQuickTab" tells Run method what macro to call: QuickRDA designates the VBA project name for my add-in, JavaCallBacks is the module name in that project, and GetQuickTab is the function name in that module. (Application.xa is the JACOB ActiveX object reference.)

If you need to look at an example of how to use JACOB to navigate the Excel Object Model, have a look at some of my open source. JEB is a Java to Excel Bridge that relies on JACOB and provides a layer of access specific to the Excel Object Model. https://github.com/erikeidt/JEB. Some of my other projects use JEB (e.g. QuickRDA), where you can see some examples of accessing Excel thru JEB if you dig around.

Calling Excel's Solver from java using JACOB

1 Answers1