Using Excel Solver from web page using Javascript

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • akristensen
    New Member
    • Aug 2007
    • 6
    #1

    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:

    [code=html]
    <html>
    <head><title> </title></head>
    <body>
    <script language="javas cript">
    function doExcelTest() {
    var oExcel = new ActiveXObject(" excel.applicati on");
    oExcel.visible = true; // Show excel
    var oBook = oExcel.Workbook s.Add();
    var oSheet = oBook.Activeshe et;

    oSheet.Range("C 2").value = "Total Weekend Employees";
    oSheet.Range("C 3").value = "=SUM(C5:C6 )";

    oSheet.Range("C 4").value = "Number Starting";
    oSheet.Range("D 4").value = "Day Emp Starts";
    oSheet.Range("E 4").value = "Friday";
    oSheet.Range("F 4").value = "Saturday";
    oSheet.Range("G 4").value = "Sunday";

    oSheet.Range("D 5").value = "Friday";
    oSheet.Range("E 5").value = "1";
    oSheet.Range("F 5").value = "1";
    oSheet.Range("G 5").value = "0";

    oSheet.Range("D 5").value = "Saturday";
    oSheet.Range("E 5").value = "0";
    oSheet.Range("F 5").value = "1";
    oSheet.Range("G 5").value = "1";

    oSheet.Range("D 8").value = "Number Working";
    oSheet.Range("E 8").value = "=SUMPRODUCT($C $5:$C$6,E5:E6)" ;
    oSheet.Range("F 8").value = "=SUMPRODUCT($C $5:$C$6,F5:F6)" ;
    oSheet.Range("G 8").value = "=SUMPRODUCT($C $5:$C$6,G5:G6)" ;

    oSheet.Range("D 9").value = ">=";

    oSheet.Range("D 10").value = "Number Needed";
    oSheet.Range("E 10").value = "25";
    oSheet.Range("F 10").value = "35";
    oSheet.Range("G 10").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:="i nteger"
    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.00 0001, AssumeLinear:=T rue, StepThru:=False , Estimates:=1, Derivatives:=1, SearchOption:=1 , IntTolerance:=5 , Scaling:=False, Convergence:=0. 0001, AssumeNonNeg:=T rue
    SolverOk SetCell:="$C$3" , MaxMinVal:=2, ValueOf:="0", ByChange:="$C$5 :$C$6"
    SolverSolve
    */
    }
    doExcelTest();
    </script>
    </body>
    </html>

    [/code]
    Tags: None
    • acoder
      Recognized Expert MVP
      • Nov 2006
      • 16032
      #2
      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?

        Comment

        • akristensen
          New Member
          • Aug 2007
          • 6
          #3
          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.

          [code=javascript]
          //Make sure we can access the solver addin
          //Forgot the MS kb article that describes this
          var oSolver = oExcel.Addins(" Solver Add-in");
          oExcel.Workbook s.Open(oSolver. FullName);
          oExcel.Workbook s(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.microso ft.com/kb/198571 for more details on accessing add-ins
          oExcel.Applicat ion.Run(oSolver .Name + "!SolverOk" , "$C$3", 2, "0", "$C$5:$C$6" );
          oExcel.Applicat ion.Run(oSolver .Name + "!SolverAdd ", "$C$5:$C$6" , 4, "integer");
          oExcel.Applicat ion.Run(oSolver .Name + "!SolverAdd ", "$E$8:$G$8" , 3, "$E$10:$G$1 0");
          oExcel.Applicat ion.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.Applicat ion.Run(oSolver .Name + "!SolverSol ve", true);
          [/code]

            Comment

            • acoder
              Recognized Expert MVP
              • Nov 2006
              • 16032
              #4
              Thanks for posting your solution.

                Comment

                Previous template Next
                Working...