Excel Solver macro from VB.NET

KingDina

I'm trying to run a Excel macro, which uses SOLVER.XLA Add-In, in VB.NET application. When the macro is executed I get the following error message

"Solver: An unexpected internal error occured, or available memeory was exhausted"

Any suggestions whats causing this error and how to debug it :confused:

Also, I get the following exception message when executing all the macros from the excel sheet

"Exception from HRESULT: 0x800xxxxx"

I'm not sure about the source of this either.

Can anyone pls help me with these errors  

---

My code for reference:

        Dim oExcel As Excel.Application
        Dim oBook As Excel.Workbook
        Dim oBooks As Excel.Workbooks
        Dim oSheets As Excel.Sheets
        Dim oSheet As Excel.Worksheet
        Try

            oExcel = CreateObject("Excel.Application")

            oBooks = oExcel.Workbooks
           
            oBook = oBooks.Open("C:\Inetpub\wwwroot\PrePackOptimizer\Data Source\book2.xls")

            oExcel.AddIns.Add("C:\Program Files\Microsoft Office\OFFICE11\Library\SOLVER\SOLVER.XLA")
           
            oExcel.DisplayAlerts = True
           
            oExcel.Run("Module1.SOLVER")

---

P.s.: The macro and the SOLVER runs perfectly when run from the EXCEL file.

Mike Hernandez - MSFT

The best place to get an answer to your macro question is in the Excel newsgroup. Please post your question here:

http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.excel.programming&lang=en&cr=US

Thanks!

Mike Hernandez
Community Program Manager
VSTO Team

Off-topic posts will be deleted

Mr.Doubt

        I've found the reason for the error, thanx to http://www.officehelp.in/

        It seems the Solver.XLA needs to be explicitly opened before it can be used. This is done automatically when we open the excel manually, but when we use objects, we have to open it using the following command 

        oExcel.Run("Solver.xla!Auto_Open")

        Once this was done my application started working as expected... The other error was also cleared...

Regards,
Dinesh

Nima Amin

I am trying to use the Solver Add-In to solve a problem.
I can run the Solver Add-In, but when I cannot use it.
For example the sample usage of Solver Add-In in Excel is like this:

SolverOk SetCell:="$B$1", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$A$3"

But When I want to use the "Solverok" as a method like this:

objExcel.SolverOk SetCell:="$B$1", MaxMinVal:=2, ValueOf:="0", ByChange:="$A$1:$A$3"

The VB give the runtime error 438 "Object does not support the Method"

What should I do

Christin-MSFT

Nima -

This forum is for people writing VB.NET or C# code, not VBA code. If you are writing VBA, then the best place to get an answer to your macro question is in the Excel newsgroup. Please post your question here: http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.excel.programming&lang=en&cr=US Thanks

Nima Amin

Christin
I have used the same code in VB.NET 2005and the same error occurs.
There is no difference between VBA and VB.NET in using of these command.

I did not understand the solution that Mr.Doubt wrote in Post 2 and I want Mr.Doubt to answer how he used the oExcel.Run("Solver.xla!Auto_Open") and I cannot mail to him
Thanks

Mr.Doubt

Nima
Actually, I was just trying to trigger a excel macro from my VB.NET application. What I had done was, I created an Excel object in my application by name oExcel and then tried to trigger the macros.

Since the macros made use of Solver add-in for its operations, it was throwing the error that I had mentioned in my first post. Then on further research I found that any add-in that is used by the excel should be explicitly opened before any operation can be performed using that add-in. Hence I used the below commad to explicitly open the solver add-in and that solved my problem.

oExcel.Run("Solver.xla!Auto_Open")

Hope this calrifies your doubt.

FYI: I was using .NET 1.1 and not .NET 2.0, if that would make any difference.

Nima Amin

Dear Dinesh
At the first I tried to use Solver Add in in my VB projects directly but I did not found any way.
So I tried to run a macro in a saved Excel file, as your project.
So I made the same Subroutine as you made.

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oBooks As Excel.Workbooks
Dim oSheets As Excel.Sheets
Dim oSheet As Excel.Worksheet

oExcel = CreateObject("Excel.Application")

oBooks = oExcel.Workbooks

oBook = oBooks.Open("C:\Documents and Settings\Amir\Desktop\db\PAPER P2.xls")

oExcel.AddIns.Add ("C:\Program Files\Microsoft Office\OFFICE11\Library\SOLVER\SOLVER.XLA")
oExcel.Visible = True
oExcel.DisplayAlerts = True
oExcel.Run ("Solver.xla!Auto_Open")
oExcel.Run ("Macro1")

but when VB want to run oExcel.Run("Solver.xla!Auto_Open"), Runtime error 1004 occurs:
"Method 'Run' of object '_Application' failed", although Excel opens successfully and the value of "oExcel.AddIns("Solver Add-in").Installed" returns True.
What should I do
Thanks & Regards
Nima

Mr.Doubt

Nima,

I don't understand why this should throw an error when you try to open the add-in. If I'm not wrong, this is what u r trying to do & getting stuck.

1. You have a macro in an Excel file, that makes use of Solver Add-in, to perform some operation.
   
2. You want to execute the Macro from a VB.NET windows application.
3. When you exec the Macro it is throwing an error.

If the above assumptions are correct, then try to pinpoint which run command is throwing the error. It might b d second one that ececutes the Macro.

You have to use the oBook object to execute the macro and not the oExcel.

oBook.Application.Run

The line in my code

oExcel.Run("Module1.SOLVER")

was the one that I had used, wrongly to open the add-in. I understood that the add-in needs to be opened explicitly and the above command will not be of any use.

Also, when you specify the macro to execute, specify it along with the excel file name like so...

oBook.Application.Run("FileName.xls!MacroName")

Regards,

Dinesh

Mojtaba

Nima,

Hi,

I have the same problem. I want to run excel solver in VB.NET. Did you find the answer

Also, are in Iran if yes we can talk to each other maybe helpful to solve the problem.

you can send me an email if you would like.

thanks in advance.