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  7311 
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);
-
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |
