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: -
<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>
-
-
3  7373  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?
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. -
//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);
-
acoder 16,027
Recognized Expert Moderator MVP
Thanks for posting your solution.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
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.
|
by: akantrowitz |
last post by:
Can you replicate the Excel solver functionality from within Csharp?
thks,
ak
|
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...
|
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
|
by: vunet.us |
last post by:
Can I get the name of a referral page using JavaScript? Just really
wondering...
| | |
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.
|
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
|
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
|
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...
|
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...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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...
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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...
| |
