Using Excel Solver from web page using Javascript

I am new to this site, so be patient if I do not ask the question correctly.

Current Target Platform: Browser: MS IE, script language: Javascript (will use VBScript, but JS is preferred), External apps: MS Excel

What I need to do: From a web page using javascript, I open a new Excel application and sheet, populate some information into it. After it is populated with some information, I need to run the "Solver..." option on the data in the excel sheet.

What I have so far: I can easily open an Excel sheet, and populate it with information from a web page using Javascript. (See the HTML code below)

What I am having problems with: I am unable to figure out how to call the "Solver..." option in excel from Javascript. (Menu Location in Excel: Tools->Solver...) With regards to the solver option in excel, once the page is populated from the web page, I am able to just go to the menu and manually run the Solver, setting the options I want, and getting the correct result. I have also created a macro in excel to do this (code snippit is located in HTML code below).

Question Restated: How do I utilize the Solver option in Excel from javascript? Any help would be greatly appreciated. (I am thinking that this question could carry over to using other add-ins in excel as well... but I am only concerned with Solver for this question)

Thank you very much in advance,
ak

HTML Code Sample:

Expand|Select|Wrap|Line Numbers

 
<html>

    <head><title></title></head>

    <body>

        <script language="javascript">

            function doExcelTest() {

                var oExcel = new ActiveXObject("excel.application");

                oExcel.visible = true;  // Show excel

                var oBook  = oExcel.Workbooks.Add();

                var oSheet = oBook.Activesheet;
 
                oSheet.Range("C2").value = "Total Weekend Employees";

                oSheet.Range("C3").value = "=SUM(C5:C6)";
 
                oSheet.Range("C4").value = "Number Starting";

                oSheet.Range("D4").value = "Day Emp Starts";

                oSheet.Range("E4").value = "Friday";

                oSheet.Range("F4").value = "Saturday";

                oSheet.Range("G4").value = "Sunday";
 
                oSheet.Range("D5").value = "Friday";

                oSheet.Range("E5").value = "1";

                oSheet.Range("F5").value = "1";

                oSheet.Range("G5").value = "0";
 
                oSheet.Range("D5").value = "Saturday";

                oSheet.Range("E5").value = "0";

                oSheet.Range("F5").value = "1";

                oSheet.Range("G5").value = "1";
 
                oSheet.Range("D8").value = "Number Working";

                oSheet.Range("E8").value = "=SUMPRODUCT($C$5:$C$6,E5:E6)";

                oSheet.Range("F8").value = "=SUMPRODUCT($C$5:$C$6,F5:F6)";

                oSheet.Range("G8").value = "=SUMPRODUCT($C$5:$C$6,G5:G6)";
 
                oSheet.Range("D9").value = ">=";
 
                oSheet.Range("D10").value = "Number Needed";

                oSheet.Range("E10").value = "25";

                oSheet.Range("F10").value = "35";

                oSheet.Range("G10").value = "12";
 
                //The line below this comment produces an error and is not correct but is close to what I want to do.

                oExcel.SolverOk("$C$3", 2, "0", "$C$5:$C$6");

/*

    ' equivalent excel macro I want to be able to do from javascript

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    SolverOk SetCell:="$C$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$5:$C$6"

    SolverAdd CellRef:="$C$5:$C$6", Relation:=4, FormulaText:="integer"

    SolverAdd CellRef:="$E$8:$G$8", Relation:=3, FormulaText:="$E$10:$G$10"

    SolverOk SetCell:="$C$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$5:$C$6"

    SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear:=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True

    SolverOk SetCell:="$C$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$5:$C$6"

    SolverSolve

*/

            }

            doExcelTest();

        </script>

    </body>

</html>

Aug 16 '07 #1

Follow Post Reply

6940

acoder

16,027

Expert Mod 8TB

ActiveX is not standard Javascript and only works in IE, so it is difficult for someone to answer unless they have programmed something similar to what you require.

Have you tried searching the documentation (if any exists) for using this ActiveXObject?

Aug 17 '07 #2

akristensen

I found a solution to my issue, so I feel that I need to post it to the forum:
I realize that this implementation is limited to IE, but works for the environment that is required for me.
Hope this solution also helps someone else.

Lines 42 and 43 in my original post #1 would be replaced by the following to make it work.

Expand|Select|Wrap|Line Numbers

 
//Make sure we can access the solver addin

//Forgot the MS kb article that describes this

var oSolver = oExcel.Addins("Solver Add-in");

oExcel.Workbooks.Open(oSolver.FullName);

oExcel.Workbooks(oSolver.Name).RunAutoMacros(1);
 
//Populate the solver with the parameters to use (copying what the excel macro produced in lines 45-54)

//See http://support.microsoft.com/kb/198571 for more details on accessing add-ins

oExcel.Application.Run(oSolver.Name + "!SolverOk", "$C$3", 2, "0", "$C$5:$C$6");

oExcel.Application.Run(oSolver.Name + "!SolverAdd", "$C$5:$C$6", 4, "integer");

oExcel.Application.Run(oSolver.Name + "!SolverAdd", "$E$8:$G$8", 3, "$E$10:$G$10");

oExcel.Application.Run(oSolver.Name + "!SolverOptions", 100, 100, 0.000001, true, false, 1, 1, 1, 5, false, 0.0001, true);
 
//Tell solver to solve the problem. Passed in a true to tell it to keep the numbers and not show the dialog box.

oExcel.Application.Run(oSolver.Name + "!SolverSolve", true);

Aug 17 '07 #3

acoder

16,027

Expert Mod 8TB

Thanks for posting your solution.

Aug 18 '07 #4

Using Excel Solver from web page using Javascript

Post your reply

Similar topics