By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

Using Excel Solver from web page using Javascript

P: 6
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
  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;
  10.  
  11.                 oSheet.Range("C2").value = "Total Weekend Employees";
  12.                 oSheet.Range("C3").value = "=SUM(C5:C6)";
  13.  
  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";
  19.  
  20.                 oSheet.Range("D5").value = "Friday";
  21.                 oSheet.Range("E5").value = "1";
  22.                 oSheet.Range("F5").value = "1";
  23.                 oSheet.Range("G5").value = "0";
  24.  
  25.                 oSheet.Range("D5").value = "Saturday";
  26.                 oSheet.Range("E5").value = "0";
  27.                 oSheet.Range("F5").value = "1";
  28.                 oSheet.Range("G5").value = "1";
  29.  
  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)";
  34.  
  35.                 oSheet.Range("D9").value = ">=";
  36.  
  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";
  41.  
  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>
  60.  
  61.  
Aug 16 '07 #1
Share this Question
Share on Google+
3 Replies


acoder
Expert Mod 15k+
P: 16,027
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

P: 6
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);
  6.  
  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);
  13.  
  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);
  16.  
Aug 17 '07 #3

acoder
Expert Mod 15k+
P: 16,027
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.