472,145 Members | 1,733 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,145 software developers and data experts.

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,

HTML Code Sample:

Expand|Select|Wrap|Line Numbers
  1. <html>
  2.     <head><title></title></head>
  3.     <body>
  4.         <script language="javascript">
  5.             function doExcelTest() {
  6.                 var oExcel = new ActiveXObject("excel.application");
  7.                 oExcel.visible = true;  // Show excel
  8.                 var oBook  = oExcel.Workbooks.Add();
  9.                 var oSheet = oBook.Activesheet;
  11.                 oSheet.Range("C2").value = "Total Weekend Employees";
  12.                 oSheet.Range("C3").value = "=SUM(C5:C6)";
  14.                 oSheet.Range("C4").value = "Number Starting";
  15.                 oSheet.Range("D4").value = "Day Emp Starts";
  16.                 oSheet.Range("E4").value = "Friday";
  17.                 oSheet.Range("F4").value = "Saturday";
  18.                 oSheet.Range("G4").value = "Sunday";
  20.                 oSheet.Range("D5").value = "Friday";
  21.                 oSheet.Range("E5").value = "1";
  22.                 oSheet.Range("F5").value = "1";
  23.                 oSheet.Range("G5").value = "0";
  25.                 oSheet.Range("D5").value = "Saturday";
  26.                 oSheet.Range("E5").value = "0";
  27.                 oSheet.Range("F5").value = "1";
  28.                 oSheet.Range("G5").value = "1";
  30.                 oSheet.Range("D8").value = "Number Working";
  31.                 oSheet.Range("E8").value = "=SUMPRODUCT($C$5:$C$6,E5:E6)";
  32.                 oSheet.Range("F8").value = "=SUMPRODUCT($C$5:$C$6,F5:F6)";
  33.                 oSheet.Range("G8").value = "=SUMPRODUCT($C$5:$C$6,G5:G6)";
  35.                 oSheet.Range("D9").value = ">=";
  37.                 oSheet.Range("D10").value = "Number Needed";
  38.                 oSheet.Range("E10").value = "25";
  39.                 oSheet.Range("F10").value = "35";
  40.                 oSheet.Range("G10").value = "12";
  42.                 //The line below this comment produces an error and is not correct but is close to what I want to do.
  43.                 oExcel.SolverOk("$C$3", 2, "0", "$C$5:$C$6");
  44. /*
  45.     ' equivalent excel macro I want to be able to do from javascript
  46.     ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  47.     SolverOk SetCell:="$C$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$5:$C$6"
  48.     SolverAdd CellRef:="$C$5:$C$6", Relation:=4, FormulaText:="integer"
  49.     SolverAdd CellRef:="$E$8:$G$8", Relation:=3, FormulaText:="$E$10:$G$10"
  50.     SolverOk SetCell:="$C$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$5:$C$6"
  51.     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
  52.     SolverOk SetCell:="$C$3", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$5:$C$6"
  53.     SolverSolve
  54. */
  55.             }
  56.             doExcelTest();
  57.         </script>
  58.     </body>
  59. </html>
Aug 16 '07 #1
3 6940
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
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
  1. //Make sure we can access the solver addin
  2. //Forgot the MS kb article that describes this
  3. var oSolver = oExcel.Addins("Solver Add-in");
  4. oExcel.Workbooks.Open(oSolver.FullName);
  5. oExcel.Workbooks(oSolver.Name).RunAutoMacros(1);
  7. //Populate the solver with the parameters to use (copying what the excel macro produced in lines 45-54)
  8. //See http://support.microsoft.com/kb/198571 for more details on accessing add-ins
  9. oExcel.Application.Run(oSolver.Name + "!SolverOk", "$C$3", 2, "0", "$C$5:$C$6");
  10. oExcel.Application.Run(oSolver.Name + "!SolverAdd", "$C$5:$C$6", 4, "integer");
  11. oExcel.Application.Run(oSolver.Name + "!SolverAdd", "$E$8:$G$8", 3, "$E$10:$G$10");
  12. oExcel.Application.Run(oSolver.Name + "!SolverOptions", 100, 100, 0.000001, true, false, 1, 1, 1, 5, false, 0.0001, true);
  14. //Tell solver to solve the problem. Passed in a true to tell it to keep the numbers and not show the dialog box.
  15. oExcel.Application.Run(oSolver.Name + "!SolverSolve", true);
Aug 17 '07 #3
16,027 Expert Mod 8TB
Thanks for posting your solution.
Aug 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

14 posts views Thread by pmud | last post: by
7 posts views Thread by Holger Fitschen | last post: by
7 posts views Thread by vunet.us | last post: by
reply views Thread by Saiars | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.