How to run the Excel Solver Macro using .Net

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);

            }