473,841 Members | 1,819 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using excel solver in VB.NET

Hi to all,
I want to use the Excel solver in a VB.Net project.

The macro

Sub Makro1Solver()
Application.Run "Solver.xla!Aut o_Open"
SolverReset
Worksheets(1).S elect
Worksheets(1).R ange("B9").Sele ct
SolverOk SetCell:="R9C2" , MaxMinVal:=3, ValueOf:="0", ByChange:="R8C2 "
SolverSolve
End Sub

works fine when starting manually in Excel.

But a message like (sorry it is in german) "Zielzelle muß eine einzelne
Zelle im aktiven Blatt sein." and "Zielzellen müssen sich in der aktiven
Tabelle befinden." appears when I start the macro from my VB.NET
project. My translation for the messages "Goalcell must be a single cell
in an active sheet" and "Goalcells must locate in the active table".

The function in the cell R9C2 is
"=Z(-8)S-Z(-7)S/(Z(-6)S+(Z(-1)S+273.15))-LN(Z(-2)S*(760/1013.25))"

My VB.NET code is

Dim ExApp As New Microsoft.Offic e.Interop.Excel .Application
Dim wb As Microsoft.Offic e.Interop.Excel .Workbook
Dim ExSheet As Microsoft.Offic e.Interop.Excel .Worksheet

Try
wb = ExApp.Workbooks .Open("E:\Daten \DD-Rechnung.xls")
ExApp.Run("SOLV ER.XLA!Auto_Ope n")

ExApp.DisplayAl erts = True
ExApp.Visible = True
wb.Activate()

ExSheet = ExApp.Worksheet s("Tabelle1")
ExSheet.Activat e()

ExApp.Run("Tabe lle1.Makro1Solv er()")

Catch ex As COMException
MessageBox.Show ("Error accessing Excel: " + ex.ToString())
Catch ex As Exception
MessageBox.Show ("Error: " + ex.ToString())
End Try

And I import in vb.net

Imports Microsoft.Offic e.Interop
Imports System.Runtime. InteropServices
Imports System.Math

A simple macro which wrote some words in the excelsheet/cell works fine,
only the solver makes problems. Also the goalseek macro in excel works
when starting manually but not when starting from vb.net and has no
error messages. It only does nothing...

I searched for several days/weeks but I couldn't find the solution.

Could someone help me please or give me a hint or has an idea how to solve.

Thanks in advance
Holger
Mar 22 '07 #1
7 14418
What its telling you is that you need to have a reference to worksheet .
You're doing your program outside of VB.Net so, you need to have a reference
to the ei ther running excel application or create a new instance of excel
and then use that reference to run your macro.

"Holger Fitschen" <fi******@tuhh. dewrote in message
news:56******** *****@mid.dfnci s.de...
Hi to all,
I want to use the Excel solver in a VB.Net project.

The macro

Sub Makro1Solver()
Application.Run "Solver.xla!Aut o_Open"
SolverReset
Worksheets(1).S elect
Worksheets(1).R ange("B9").Sele ct
SolverOk SetCell:="R9C2" , MaxMinVal:=3, ValueOf:="0", ByChange:="R8C2 "
SolverSolve
End Sub

works fine when starting manually in Excel.

But a message like (sorry it is in german) "Zielzelle muß eine einzelne
Zelle im aktiven Blatt sein." and "Zielzellen müssen sich in der aktiven
Tabelle befinden." appears when I start the macro from my VB.NET project.
My translation for the messages "Goalcell must be a single cell in an
active sheet" and "Goalcells must locate in the active table".

The function in the cell R9C2 is
"=Z(-8)S-Z(-7)S/(Z(-6)S+(Z(-1)S+273.15))-LN(Z(-2)S*(760/1013.25))"

My VB.NET code is

Dim ExApp As New Microsoft.Offic e.Interop.Excel .Application
Dim wb As Microsoft.Offic e.Interop.Excel .Workbook
Dim ExSheet As Microsoft.Offic e.Interop.Excel .Worksheet

Try
wb = ExApp.Workbooks .Open("E:\Daten \DD-Rechnung.xls")
ExApp.Run("SOLV ER.XLA!Auto_Ope n")

ExApp.DisplayAl erts = True
ExApp.Visible = True
wb.Activate()

ExSheet = ExApp.Worksheet s("Tabelle1")
ExSheet.Activat e()

ExApp.Run("Tabe lle1.Makro1Solv er()")

Catch ex As COMException
MessageBox.Show ("Error accessing Excel: " + ex.ToString())
Catch ex As Exception
MessageBox.Show ("Error: " + ex.ToString())
End Try

And I import in vb.net

Imports Microsoft.Offic e.Interop
Imports System.Runtime. InteropServices
Imports System.Math

A simple macro which wrote some words in the excelsheet/cell works fine,
only the solver makes problems. Also the goalseek macro in excel works
when starting manually but not when starting from vb.net and has no error
messages. It only does nothing...

I searched for several days/weeks but I couldn't find the solution.

Could someone help me please or give me a hint or has an idea how to
solve.

Thanks in advance
Holger
Mar 23 '07 #2
I think I don't really understand what you mean.
I made a new instance of Excel with:

Dim ExApp As New Microsoft.Offic e.Interop.Excel .Application

and then I use a reference to excel, which starts excel and runs simple
macros, but not the solver and not the GoalSeek funktion.

btw I use Visual Studio 1.1, VSTO 2003 and Excel 2003

Could it be, that the solver couldn't read a function in a cell when it
started from external??

Thanks in advance
Holger

Richard T. Edwards schrieb:
What its telling you is that you need to have a reference to worksheet .
You're doing your program outside of VB.Net so, you need to have a
reference to the ei ther running excel application or create a new
instance of excel and then use that reference to run your macro.

"Holger Fitschen" <fi******@tuhh. dewrote in message
news:56******** *****@mid.dfnci s.de...
>Hi to all,
I want to use the Excel solver in a VB.Net project.

The macro

Sub Makro1Solver()
Application.Run "Solver.xla!Aut o_Open"
SolverReset
Worksheets(1).S elect
Worksheets(1).R ange("B9").Sele ct
SolverOk SetCell:="R9C2" , MaxMinVal:=3, ValueOf:="0",
ByChange:="R8C 2"
SolverSolve
End Sub

works fine when starting manually in Excel.

But a message like (sorry it is in german) "Zielzelle muß eine
einzelne Zelle im aktiven Blatt sein." and "Zielzellen müssen sich in
der aktiven Tabelle befinden." appears when I start the macro from my
VB.NET project. My translation for the messages "Goalcell must be a
single cell in an active sheet" and "Goalcells must locate in the
active table".

The function in the cell R9C2 is
"=Z(-8)S-Z(-7)S/(Z(-6)S+(Z(-1)S+273.15))-LN(Z(-2)S*(760/1013.25))"

My VB.NET code is

Dim ExApp As New Microsoft.Offic e.Interop.Excel .Application
Dim wb As Microsoft.Offic e.Interop.Excel .Workbook
Dim ExSheet As Microsoft.Offic e.Interop.Excel .Worksheet

Try
wb = ExApp.Workbooks .Open("E:\Daten \DD-Rechnung.xls")
ExApp.Run("SOLV ER.XLA!Auto_Ope n")

ExApp.DisplayAl erts = True
ExApp.Visible = True
wb.Activate()

ExSheet = ExApp.Worksheet s("Tabelle1")
ExSheet.Activat e()

ExApp.Run("Tabe lle1.Makro1Solv er()")

Catch ex As COMException
MessageBox.Show ("Error accessing Excel: " + ex.ToString())
Catch ex As Exception
MessageBox.Show ("Error: " + ex.ToString())
End Try

And I import in vb.net

Imports Microsoft.Offic e.Interop
Imports System.Runtime. InteropServices
Imports System.Math

A simple macro which wrote some words in the excelsheet/cell works
fine, only the solver makes problems. Also the goalseek macro in excel
works when starting manually but not when starting from vb.net and has
no error messages. It only does nothing...

I searched for several days/weeks but I couldn't find the solution.

Could someone help me please or give me a hint or has an idea how to
solve.

Thanks in advance
Holger
Mar 23 '07 #3
btw I use ...Excel 2003
SolverOk SetCell:="R9C2" , ...
Hi. Not sure on the German version, but in US versions of Excel, Solver
references need to be in A1 notation. (Starting with Excel 97 I believe)

SolverOk SetCell:="B9", ...

As a side note, I am a little curious on your equation.
Is it working ok?
=Z(-8)S-Z(-7)S/(Z(-6)S ...

It looks to me like it should be:
"=Z*(-8)*S-Z*(-7)*S ...
--
Dana DeLouis
Windows XP & Office 2007
"Holger Fitschen" <fi******@tuhh. dewrote in message
news:46******** ******@tuhh.de. ..
>I think I don't really understand what you mean.
I made a new instance of Excel with:

Dim ExApp As New Microsoft.Offic e.Interop.Excel .Application

and then I use a reference to excel, which starts excel and runs simple
macros, but not the solver and not the GoalSeek funktion.

btw I use Visual Studio 1.1, VSTO 2003 and Excel 2003

Could it be, that the solver couldn't read a function in a cell when it
started from external??

Thanks in advance
Holger

Richard T. Edwards schrieb:
>What its telling you is that you need to have a reference to worksheet .
You're doing your program outside of VB.Net so, you need to have a
reference to the ei ther running excel application or create a new
instance of excel and then use that reference to run your macro.

"Holger Fitschen" <fi******@tuhh. dewrote in message
news:56******* ******@mid.dfnc is.de...
>>Hi to all,
I want to use the Excel solver in a VB.Net project.

The macro

Sub Makro1Solver()
Application.Run "Solver.xla!Aut o_Open"
SolverReset
Worksheets(1).S elect
Worksheets(1).R ange("B9").Sele ct
SolverOk SetCell:="R9C2" , MaxMinVal:=3, ValueOf:="0",
ByChange:="R8 C2"
SolverSolve
End Sub

works fine when starting manually in Excel.

But a message like (sorry it is in german) "Zielzelle muß eine einzelne
Zelle im aktiven Blatt sein." and "Zielzellen müssen sich in der aktiven
Tabelle befinden." appears when I start the macro from my VB.NET
project. My translation for the messages "Goalcell must be a single cell
in an active sheet" and "Goalcells must locate in the active table".

The function in the cell R9C2 is
"=Z(-8)S-Z(-7)S/(Z(-6)S+(Z(-1)S+273.15))-LN(Z(-2)S*(760/1013.25))"

My VB.NET code is

Dim ExApp As New Microsoft.Offic e.Interop.Excel .Application
Dim wb As Microsoft.Offic e.Interop.Excel .Workbook
Dim ExSheet As Microsoft.Offic e.Interop.Excel .Worksheet

Try
wb = ExApp.Workbooks .Open("E:\Daten \DD-Rechnung.xls")
ExApp.Run("SOLV ER.XLA!Auto_Ope n")

ExApp.DisplayAl erts = True
ExApp.Visible = True
wb.Activate()

ExSheet = ExApp.Worksheet s("Tabelle1")
ExSheet.Activat e()

ExApp.Run("Tabe lle1.Makro1Solv er()")

Catch ex As COMException
MessageBox.Show ("Error accessing Excel: " + ex.ToString())
Catch ex As Exception
MessageBox.Show ("Error: " + ex.ToString())
End Try

And I import in vb.net

Imports Microsoft.Offic e.Interop
Imports System.Runtime. InteropServices
Imports System.Math

A simple macro which wrote some words in the excelsheet/cell works fine,
only the solver makes problems. Also the goalseek macro in excel works
when starting manually but not when starting from vb.net and has no
error messages. It only does nothing...

I searched for several days/weeks but I couldn't find the solution.

Could someone help me please or give me a hint or has an idea how to
solve.

Thanks in advance
Holger

Mar 23 '07 #4
Hi Dana,
yes, the function works in both notations (A1 and Z$S$)
But how I said, only when I start it manually, not out of vb.net.

I very distressed not to find a solution.....

Dana DeLouis schrieb:
>btw I use ...Excel 2003
SolverOk SetCell:="R9C2" , ...

Hi. Not sure on the German version, but in US versions of Excel, Solver
references need to be in A1 notation. (Starting with Excel 97 I believe)

SolverOk SetCell:="B9", ...

As a side note, I am a little curious on your equation.
Is it working ok?
=Z(-8)S-Z(-7)S/(Z(-6)S ...
That means a relativ position to the dependent cell
R9C2 = B9 and Z(-8) = B1 .
This happens automatically when I switched from A1 to Z$S$ notation for
testing several capabilities to solve my problem.
>
It looks to me like it should be:
"=Z*(-8)*S-Z*(-7)*S ...
Mar 23 '07 #5
I've learned that =Z(-8)S is the German equivalent to RC notation =R[-8]C.
I would experiment and switch everything (Worksheet & code) to the
equivalent A1 notation.

--
Dana DeLouis
Windows XP & Office 2007


Mar 23 '07 #6
In my version of Excel 2003 the application addins don't load when Excel is
run as a COM server. But this can be over come by explicitly loading in the
startup routine or by COM reference method. My example uses an addin called
dataline follows:

AddIns("PI-DataLink").Inst alled = True

This could be your problem also.

John Fors
"Dana DeLouis" <dd******@bells outh.netwrote in message
news:eH******** *****@TK2MSFTNG P04.phx.gbl...
I've learned that =Z(-8)S is the German equivalent to RC notation =R[-8]C.
I would experiment and switch everything (Worksheet & code) to the
equivalent A1 notation.

--
Dana DeLouis
Windows XP & Office 2007


Mar 26 '07 #7
Thanks to all,
I solved it by using a VBA subroutine "Worksheet_Chan ge" which monitored
a cell. In this cell I wrote with vb.net a "1" and the sub
"Worksheet_Chan ge" starts the solver.
It not a beautiful code, but it works.

Holger

John J Fors schrieb:
In my version of Excel 2003 the application addins don't load when Excel is
run as a COM server. But this can be over come by explicitly loading in the
startup routine or by COM reference method. My example uses an addin called
dataline follows:

AddIns("PI-DataLink").Inst alled = True

This could be your problem also.

John Fors
"Dana DeLouis" <dd******@bells outh.netwrote in message
news:eH******** *****@TK2MSFTNG P04.phx.gbl...
>I've learned that =Z(-8)S is the German equivalent to RC notation =R[-8]C.
I would experiment and switch everything (Worksheet & code) to the
equivalent A1 notation.

--
Dana DeLouis
Windows XP & Office 2007


Mar 28 '07 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
11048
by: Dan | last post by:
Hello, I am trying to read and write to an Excel file via my Java applet. I have done so successfully on several simple Excel files that simply had data cells without many complicated equations or any other Excel extras. However, now is the time to get the complicated ones to work. I didn't think that there would be a difference, but there is. At first, I was beginning to think that it was a file size problem, but now I am unsure. ...
1
7055
by: Aaron | last post by:
I am trying to write a Python script file which analyses data in an iterative manner. During each iteration, I wish to utilise the Solver function in Excel to perform some constrained, non-linear fitting to the data. I have a VBA macro which runs the Solver, and am using self.xlapp = win32com.client.dynamic.Dispatch("Excel.Application") .. .. .. self.xlapp.run(macro)
0
1632
by: Josh Golden | last post by:
has anyone every used the solver add-in for Excel? it allows you to solve linear and non-linear problems given certain conditions. I was wondering if something like this exists already in .NET? In an application I would like to solve a model with 500 variables. Excel-Solver can do this but I want to do it all in code. any thoughts are appreciated! Josh
3
6997
by: Mr.Doubt | last post by:
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??? Also, I get the following exception message when executing all the
0
1782
by: engsolnorm | last post by:
A co-worker and I want to increase our knowledge of Python. I have a tiny bit of exposure to python. He has none, but has wide experience with C, C++, NET, C#, etc. We agreed we'd do a Sudoku solver. I'd do the GUI, he would do the solver code. My question is this: I assume that once I invoke mauinloop, the loop just cycles between bound events...(if not true, please tell me)...so how do I "branch" out to the solver code, which will be in...
0
1873
by: akantrowitz | last post by:
Can you replicate the Excel solver functionality from within Csharp? thks, ak
2
3269
by: tristan.chaplin | last post by:
Is it possible to use the Excel Equation Solver in an Access Database, ie call it from vba code? Thanks, Tristan
1
2338
by: Mike Heywood | last post by:
Hi, I am currently trying to automate a process that I have been studying the manual results from for a while. The process simply identifies events that meet certain criteria and at the moment it then requires human interaction to decide what to do next (is a seperate process run or not). I have set up the process to do this automatically based on a couple of variables calculated but I am having a few problems optimising the data to...
3
7393
by: akristensen | last post by:
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...
0
9865
marktang
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...
0
9706
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10927
Oralloy
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...
0
10609
jinu1996
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...
1
10667
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10303
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5691
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...
0
5880
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4498
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 using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.