Monday, August 01, 2011

How to run the Excel Solver Macro using .Net

Posted by Rahul Kharde at 2:40 AM
Hi Friends,
To run the Excel Solver macro, it required follows 

1) Excel is located in C:\Program Files\Microsoft Office\Office. Click the “EXCEL” icon to open it. Alternatively, use the search tool accessible from the start button on your desktop to locate “EXCEL”.

2) Once Excel is opened, you may need load Solver into Excel. Click the “Tools” button in the Excel banner menu. If you find Solver in the Tools menu, you’re ready to use Solver. If you do not find Solver in the Tools menu, then proceed to Step 3 below.

3) First, in Excel, click Tools->Macro->Security and set the Security level be medium (otherwise you will not be able to open Solver).

4) Next go to C:\Program Files\Microsoft Office\Office\Library\Solver and click on “SOLVER”, it will automatically be added to Excel. Alternatively, use the search tool accessible from the start button on your desktop to locate “Solver”. Click on “Solver”, it will automatically be added to Excel

5) Verify that Solver has been installed by returning to Excel. Click on the Excel Tools button and locate Solver in the Tools menu. To run solver just click on this menu item

Write macro Solver code in excel,
Below is the example of solver which runs on excel sheet.

Sub FunRunSolver()
     Dim CellRange
     CellRange = Range("$F$9")
     SolverOk SetCell:="$F$8", MaxMinVal:=3, ValueOf:= CellRange,ByChange:="$L$21"             
    SolverSolve UserFinish:=True
End Sub

At first you need to set the reference to excel in .net:
Click on References, add reference.
Click the .com button and browse to the Microsoft office Folder in which the Excel.exe is located.

Add it. (This also works with some other parts of the office package)

Now you should see EXCEL in your references
My code for reference:


            using Excel = Microsoft.Office.Interop.Excel;
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook excelBook = excelApp.Workbooks.Open(sheetPath, 0,false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", true, true, 0, true, true, false);
            Excel._Worksheet excelWorksheet = (Excel._Worksheet)excelBook1.Worksheets.get_Item("ExcelsheetName");
            RunMacro(excelApp, new Object[] { "MacroName" });
            private void RunMacro(object oApp, object[] oRunArgs)
            {
                oApp.GetType().InvokeMember("Run",
                System.Reflection.BindingFlags.Default |
                System.Reflection.BindingFlags.InvokeMethod,
                null, oApp, oRunArgs);
            }


If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top