473,706 Members | 2,380 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using Excel Solver from web page using Javascript

6 New Member
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
3 7371
acoder
16,027 Recognized Expert Moderator MVP
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
6 New Member
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
16,027 Recognized Expert Moderator MVP
Thanks for posting your solution.
Aug 18 '07 #4

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

Similar topics

14
5797
by: pmud | last post by:
Hi, I need to use an Excel Sheet in ASP.NET application so that the users can enter (copy, paste ) large number of rows in this Excel Sheet. Also, Whatever the USER ENETRS needs to go to the SQL DATABASE, probably by the click of a button. Is this possible? & what is the BEST APPROACH for doing this? & also if any links are there do tell those to me too coz I have no idea how to go about doing it.
0
1851
by: akantrowitz | last post by:
Can you replicate the Excel solver functionality from within Csharp? thks, ak
1
2264
by: iqedgeman | last post by:
I am using an excel spreadsheet to insert content for a report. I can access an excel spreadsheet ("sheet1") without any problems and change the rows and columns. And I can save my spreadsheet as an excel document. Is there a command in VB that enables you to save the document as a web page? If I get out of my program and open the created excel document using excel, I can save the document for the web. This works fine, but I need to do...
7
14393
by: Holger Fitschen | last post by:
Hi to all, I want to use the Excel solver in a VB.Net project. The macro Sub Makro1Solver() Application.Run "Solver.xla!Auto_Open" SolverReset Worksheets(1).Select Worksheets(1).Range("B9").Select
7
6232
by: vunet.us | last post by:
Can I get the name of a referral page using JavaScript? Just really wondering...
0
1257
by: th12345 | last post by:
Hi All, I want to create custom excel sheet page in my web application like how Google spreadsheet was created. I do not want load data into Microsoft excel sheet. Please give some idea on this, how do start my work . Either create client side table using javascript or Server side table using Gridview or Server Table. If anyone have same experience on this, Please suggest me how do approach.
2
1545
by: John | last post by:
I am trying to export a report to Excel, but the Page Header is missing. I have successfully exported to Adobe .PDF and HTML, for for some reason I can not get it to work in Excel. I am using Crystal Report XI R2. Thank you in advance. John
0
1525
by: venson | last post by:
Hello everyone, I am working on a project right now and I needed excel solver to maximize the problem The project is about a decision support system, I am trying to do it on Visual Basic 2008. I am wondering is it possible to click on my VB and the input will goes to excel and run solver? Because solver contains some macro code, so I don't know how to do it and if it's possible. Thank you very much for the help
0
8781
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8696
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9285
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8993
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7906
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6614
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4448
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3147
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2094
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.