AnsweredAssumed Answered

vrf Using MS Excel Solver from a VEE 6.0 Program

Question asked by gregwale on Oct 31, 2004
Latest reply on Nov 1, 2004 by VRFuser

Like • Show 0 Likes0
Comment • 4

<html><head><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 10 (filtered)"><style></style></head><body lang=EN-US link=blue vlink=purple><div class=Section1>Hi Nick, I sell an add-in to VEE called Vxl forexactly what you want: connecting data to Excel. I also ship numerous VEEexamples including calling an Excel macro. It is for sale on my website. Also on myhome page click on Technical Articles if you want to see an article on ActiveXfrom VEE 5 in case you want to try writing in ActiveX yourself. Best regards,Greg Walewww.passtechnologies.com <div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'>-----Original Message-----
From: Guzzardo, Nick[mailto:NBG@ssitech.com]
Sent: Friday, October 29, 2004 11:04 AM<fontsize=2 face=Tahoma>
Subject: Using MS Excel Solverfrom a VEE 6.0 Program I need to access the Solver add-in function in MS Excel froma VEE 6.0 program to solve a system of 4 non-linear equations. Specifically, I am taking 4 resistance measurements from a resistor bridge andneed to calculate the individual resistor values within the bridge. I have written a macro in Excel to simplify the processwithin Excel, so I need to be able to insert data to a spreadsheet, run mymacro, and extract the results. I know that you can access just aboutanything in Excel using ActiveX, but I have never used it before this. Ihave seen the ActiveX examples, but they have not gotten me past the insertionand extraction of the data. My macro is listed below: Sub WheatstoneSolver()'' WheatstoneSolver Macro'' Keyboard Shortcut: Ctrl+l' SolverReset SolverLoad LoadArea:="$A$12:$A$18" SolverOptions MaxTime:=100,Iterations:=100, Precision:=0.000001, AssumeLinear _ :=False, StepThru:=False,Estimates:=1, Derivatives:=1, SearchOption:=1, _ IntTolerance:=5,Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False SolverOk SetCell:="$D$6", MaxMinVal:=3,ValueOf:="0", ByChange:="$B$1:$B$4" SolverSolve userFinish:=TrueEnd Sub Does anyone know how to access Excel macros or even accessthe Excel Solver directly from within VEE? Are there any free resources on the internet for usingActiveX? Nick G.SSI Technologies, Inc.608-373-2234 </div></div>--- You are currently subscribed to vrf as: rsb@soco.agilent.com To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com". To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com". To send messages to this mailing list, email "vrf@agilent.com". If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".</BODY></html>

No one else has this question

Outcomes

VRFuser Oct 28, 2004 9:04 PM

<html><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 10 (filtered)"><style></style></head><body lang=EN-US link=blue vlink=purple><div class=Section1>I need to access the Solver add-in function in MS Excel froma VEE 6.0 program to solve a system of 4 non-linear equations. Specifically,I am taking 4 resistance measurements from a resistor bridge and need tocalculate the individual resistor values within the bridge. I have written a macro in Excel to simplify the processwithin Excel, so I need to be able to insert data to a spreadsheet, run mymacro, and extract the results. I know that you can access just aboutanything in Excel using ActiveX, but I have never used it before this. Ihave seen the ActiveX examples, but they have not gotten me past the insertionand extraction of the data. My macro is listed below: Sub WheatstoneSolver()'' WheatstoneSolver Macro'' Keyboard Shortcut: Ctrl+l' SolverReset SolverLoad LoadArea:="$A$12:$A$18" SolverOptions MaxTime:=100,Iterations:=100, Precision:=0.000001, AssumeLinear _ :=False,StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ IntTolerance:=5,Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False SolverOk SetCell:="$D$6",MaxMinVal:=3, ValueOf:="0", ByChange:="$B$1:$B$4" SolverSolve userFinish:=TrueEnd Sub Does anyone know how to access Excel macros or even accessthe Excel Solver directly from within VEE? Are there any free resources on the internet for usingActiveX? Nick G.SSI Technologies, Inc.608-373-2234 </div>--- You are currently subscribed to vrf as: rsb@soco.agilent.com To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com". To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com". To send messages to this mailing list, email "vrf@agilent.com". If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".</BODY></html>

Actions

VRFuser Oct 28, 2004 10:09 PM

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><HTML><HEAD><META http-equiv=Content-Type content="text/html; charset=iso-8859-1"><META content="MSHTML 6.00.2800.1476" name=GENERATOR><STYLE></STYLE></HEAD><BODY bgColor=#ffffff><DIV>Hello all/Agilent</DIV><DIV> </DIV><DIV>Why is it that sometimes, when a Start button is clicked to run a section of code, the containing window closes? I then have to waste time finding the window I was working in.</DIV><DIV> </DIV><DIV>Argghhh!!!</DIV><DIV> </DIV><DIV>This has been so for a VEE versions of 6 & 7 at least. This doesn't happen all the time and I have not noticed a pattern with this behaviour.</DIV><DIV> </DIV><DIV>Can this be submitted as a bug report to Agilent and hopefully be fixed at some time?</DIV><DIV> </DIV><DIV>Thanks</DIV><DIV> </DIV><DIV>Andrew Fudge</DIV><DIV>Software Engineer</DIV><DIV>Wales, UK</DIV>--- You are currently subscribed to vrf as: rsb@soco.agilent.com To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com". To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com". To send messages to this mailing list, email "vrf@agilent.com". If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".</BODY></HTML>

Actions

VRFuser Nov 1, 2004 2:42 AM

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><HTML><HEAD><META http-equiv=Content-Type content="text/html; charset=windows-1252"><META content="MSHTML 6.00.2800.1458" name=GENERATOR></HEAD><BODY style="COLOR: #000000; FONT-FAMILY: Arial" bgColor=#ffffff><DIV>Greetings Amrik,</DIV><DIV> </DIV><DIV>The default installation for VEE places configuration files in different folders for both the development and runtime installations. (It also does this for each different Window's user on that PC.) This can create a large number of configuration files that are NOT necessarily configured the same! This is a common place to check if something works in the development version, but not in runtime. (Or for one user, but not another.)</DIV><DIV><DIV> </DIV><DIV>The easiest way to have all versions on one PC use a single set of common configuration files is to use the HOME environment variable to point to the folder where these files are to be kept.</DIV><DIV> </DIV><DIV>More information on "HOME" can be found in the VEE help files.</DIV><DIV> </DIV><DIV>Cheers,</DIV><DIV>Mike Groves</DIV></DIV><BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px"> <DIV class=OutlookMessageHeader dir=ltr align=left>-----Original Message----- From: Amrik Sekhon [mailto:Amrik.Sekhon@densitron.co.uk] Sent: Monday, November 01, 2004 4:05 AM To: VRF Subject: [vrf] RE: Runtime Error </DIV> <DIV dir=ltr align=left> </DIV> <DIV></DIV> <DIV>Hi all</DIV> <DIV> </DIV> <DIV>I have created a runtime version of my prog, but when i try to execute it i get the error shown in the attachment. It seems to be losing some IO information, not sure what s going wrong. </DIV> <DIV> </DIV> <DIV>Cheers</DIV> <DIV> </DIV> <DIV>Amrik</DIV></BLOCKQUOTE>--- You are currently subscribed to vrf as: rsb@soco.agilent.com To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com". To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com". To send messages to this mailing list, email "vrf@agilent.com". If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".</BODY></HTML>

Actions

VRFuser Nov 1, 2004 5:43 AM

Thanks for the help Doug. While waiting for an answer that I could use
I ended up brute forcing the program without ActiveX. The trick was in
sending the control key to start the macro. I eventually found from a
3rd part site that I should use "^L" for ctrl-L. As long as my Excel
window was the window in focus, the macro ran fine. Now if I could get
it to run with or without it being in focus!

Thanks again for the example program.

Nick G.

-----Original Message-----
From: decote@verizon.net [mailto:decote@verizon.net]
Sent: Monday, November 01, 2004 6:43 PM
To: VRF
Subject: [vrf] RE: Using MS Excel Solver from a VEE 6.0 Program

Hello Nick,

I have started on a project similar to yours; I have data in excel files
extracted from a Vee controlled measurement system. I need to take the
excel data which is in text format, create a series of graphs and resave
the data as an excel work book. I started by writing a macro while in
excel tools, so I have a complete set of steps necessary to complete the
work, now I just have to get those steps into my new Vee program. I
looked at the examples supplied with Vee and they are some help but not
a lot. I do have something else, my original vee program has commands in
it to open a new excel file, write headers, save date and close the
files. I plan to use these as a basis of my program and work it out from
there. I also have this nice book, Excel Programming, Read Less- Learn
More, from MaranGraphics, www.wiley.com/compbooks, which is full of good
information. Attached is a portion of the Vee programming with the excel
macro commands in it. You should be able to use the same Ve
e commands and copy and paste your program directly into them as I am
doing. Hope this helps, good luck.

Doug

************************************************************************
***************
-----Original Message-----
From: Guzzardo, Nick [mailto:NBG@ssitech.com]
Sent: Friday, October 29, 2004 11:04 AM
Subject: Using MS Excel Solver from a VEE 6.0 Program

I need to access the Solver add-in function in MS Excel from a VEE 6.0
program to solve a system of 4 non-linear equations. Specifically, I am
taking 4 resistance measurements from a resistor bridge and need to
calculate the individual resistor values within the bridge.

I have written a macro in Excel to simplify the process within Excel, so
I need to be able to insert data to a spreadsheet, run my macro, and
extract the results. I know that you can access just about anything in
Excel using ActiveX, but I have never used it before this. I have seen
the ActiveX examples, but they have not gotten me past the insertion and
extraction of the data.

My macro is listed below:

Sub WheatstoneSolver()
'
' WheatstoneSolver Macro
'
' Keyboard Shortcut: Ctrl+l
'
    SolverReset
    SolverLoad LoadArea:="$A$12:$A$18"
    SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
        :=False, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
        IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=False
    SolverOk SetCell:="$D$6", MaxMinVal:=3, ValueOf:="0",
ByChange:="$B$1:$B$4"
    SolverSolve userFinish:=True
End Sub

Does anyone know how to access Excel macros or even access the Excel
Solver directly from within VEE?

Are there any free resources on the internet for using ActiveX?

Nick G.
SSI Technologies, Inc.
608-373-2234

---
You are currently subscribed to vrf as: decote@verizon.net
To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com".
To unsubscribe send a blank email to
"leave-vrf@it.lists.it.agilent.com".
To send messages to this mailing list, email "vrf@agilent.com".
If you need help with the mailing list send a message to
"owner-vrf@it.lists.it.agilent.com".

---
You are currently subscribed to vrf as: rsb@soco.agilent.com
To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com".
To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com".
To send messages to this mailing list, email "vrf@agilent.com".
If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".

Actions

4 Replies

VRFuser Oct 28, 2004 9:04 PM
Mark Correct
Correct Answer
<html><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 10 (filtered)"><style></style></head><body lang=EN-US link=blue vlink=purple><div class=Section1>I need to access the Solver add-in function in MS Excel froma VEE 6.0 program to solve a system of 4 non-linear equations. Specifically,I am taking 4 resistance measurements from a resistor bridge and need tocalculate the individual resistor values within the bridge. I have written a macro in Excel to simplify the processwithin Excel, so I need to be able to insert data to a spreadsheet, run mymacro, and extract the results. I know that you can access just aboutanything in Excel using ActiveX, but I have never used it before this. Ihave seen the ActiveX examples, but they have not gotten me past the insertionand extraction of the data. My macro is listed below: Sub WheatstoneSolver()'' WheatstoneSolver Macro'' Keyboard Shortcut: Ctrl+l' SolverReset SolverLoad LoadArea:="$A$12:$A$18" SolverOptions MaxTime:=100,Iterations:=100, Precision:=0.000001, AssumeLinear _ :=False,StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _ IntTolerance:=5,Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False SolverOk SetCell:="$D$6",MaxMinVal:=3, ValueOf:="0", ByChange:="$B$1:$B$4" SolverSolve userFinish:=TrueEnd Sub Does anyone know how to access Excel macros or even accessthe Excel Solver directly from within VEE? Are there any free resources on the internet for usingActiveX? Nick G.SSI Technologies, Inc.608-373-2234 </div>--- You are currently subscribed to vrf as: rsb@soco.agilent.com To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com". To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com". To send messages to this mailing list, email "vrf@agilent.com". If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".</BODY></html>
Like • Show 0 Likes0
Actions
VRFuser Oct 28, 2004 10:09 PM
Mark Correct
Correct Answer
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><HTML><HEAD><META http-equiv=Content-Type content="text/html; charset=iso-8859-1"><META content="MSHTML 6.00.2800.1476" name=GENERATOR><STYLE></STYLE></HEAD><BODY bgColor=#ffffff><DIV>Hello all/Agilent</DIV><DIV> </DIV><DIV>Why is it that sometimes, when a Start button is clicked to run a section of code, the containing window closes? I then have to waste time finding the window I was working in.</DIV><DIV> </DIV><DIV>Argghhh!!!</DIV><DIV> </DIV><DIV>This has been so for a VEE versions of 6 & 7 at least. This doesn't happen all the time and I have not noticed a pattern with this behaviour.</DIV><DIV> </DIV><DIV>Can this be submitted as a bug report to Agilent and hopefully be fixed at some time?</DIV><DIV> </DIV><DIV>Thanks</DIV><DIV> </DIV><DIV>Andrew Fudge</DIV><DIV>Software Engineer</DIV><DIV>Wales, UK</DIV>--- You are currently subscribed to vrf as: rsb@soco.agilent.com To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com". To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com". To send messages to this mailing list, email "vrf@agilent.com". If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".</BODY></HTML>
Like • Show 0 Likes0
Actions
VRFuser Nov 1, 2004 2:42 AM
Mark Correct
Correct Answer
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><HTML><HEAD><META http-equiv=Content-Type content="text/html; charset=windows-1252"><META content="MSHTML 6.00.2800.1458" name=GENERATOR></HEAD><BODY style="COLOR: #000000; FONT-FAMILY: Arial" bgColor=#ffffff><DIV>Greetings Amrik,</DIV><DIV> </DIV><DIV>The default installation for VEE places configuration files in different folders for both the development and runtime installations. (It also does this for each different Window's user on that PC.) This can create a large number of configuration files that are NOT necessarily configured the same! This is a common place to check if something works in the development version, but not in runtime. (Or for one user, but not another.)</DIV><DIV><DIV> </DIV><DIV>The easiest way to have all versions on one PC use a single set of common configuration files is to use the HOME environment variable to point to the folder where these files are to be kept.</DIV><DIV> </DIV><DIV>More information on "HOME" can be found in the VEE help files.</DIV><DIV> </DIV><DIV>Cheers,</DIV><DIV>Mike Groves</DIV></DIV><BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px"> <DIV class=OutlookMessageHeader dir=ltr align=left>-----Original Message----- From: Amrik Sekhon [mailto:Amrik.Sekhon@densitron.co.uk] Sent: Monday, November 01, 2004 4:05 AM To: VRF Subject: [vrf] RE: Runtime Error </DIV> <DIV dir=ltr align=left> </DIV> <DIV></DIV> <DIV>Hi all</DIV> <DIV> </DIV> <DIV>I have created a runtime version of my prog, but when i try to execute it i get the error shown in the attachment. It seems to be losing some IO information, not sure what s going wrong. </DIV> <DIV> </DIV> <DIV>Cheers</DIV> <DIV> </DIV> <DIV>Amrik</DIV></BLOCKQUOTE>--- You are currently subscribed to vrf as: rsb@soco.agilent.com To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com". To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com". To send messages to this mailing list, email "vrf@agilent.com". If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".</BODY></HTML>
Like • Show 0 Likes0
Actions
VRFuser Nov 1, 2004 5:43 AM
Mark Correct
Correct Answer
Thanks for the help Doug. While waiting for an answer that I could use
I ended up brute forcing the program without ActiveX. The trick was in
sending the control key to start the macro. I eventually found from a
3rd part site that I should use "^L" for ctrl-L. As long as my Excel
window was the window in focus, the macro ran fine. Now if I could get
it to run with or without it being in focus!

Thanks again for the example program.

Nick G.

-----Original Message-----
From: decote@verizon.net [mailto:decote@verizon.net]
Sent: Monday, November 01, 2004 6:43 PM
To: VRF
Subject: [vrf] RE: Using MS Excel Solver from a VEE 6.0 Program

Hello Nick,

I have started on a project similar to yours; I have data in excel files
extracted from a Vee controlled measurement system. I need to take the
excel data which is in text format, create a series of graphs and resave
the data as an excel work book. I started by writing a macro while in
excel tools, so I have a complete set of steps necessary to complete the
work, now I just have to get those steps into my new Vee program. I
looked at the examples supplied with Vee and they are some help but not
a lot. I do have something else, my original vee program has commands in
it to open a new excel file, write headers, save date and close the
files. I plan to use these as a basis of my program and work it out from
there. I also have this nice book, Excel Programming, Read Less- Learn
More, from MaranGraphics, www.wiley.com/compbooks, which is full of good
information. Attached is a portion of the Vee programming with the excel
macro commands in it. You should be able to use the same Ve
e commands and copy and paste your program directly into them as I am
doing. Hope this helps, good luck.

Doug

************************************************************************
***************
-----Original Message-----
From: Guzzardo, Nick [mailto:NBG@ssitech.com]
Sent: Friday, October 29, 2004 11:04 AM
Subject: Using MS Excel Solver from a VEE 6.0 Program

I need to access the Solver add-in function in MS Excel from a VEE 6.0
program to solve a system of 4 non-linear equations. Specifically, I am
taking 4 resistance measurements from a resistor bridge and need to
calculate the individual resistor values within the bridge.

I have written a macro in Excel to simplify the process within Excel, so
I need to be able to insert data to a spreadsheet, run my macro, and
extract the results. I know that you can access just about anything in
Excel using ActiveX, but I have never used it before this. I have seen
the ActiveX examples, but they have not gotten me past the insertion and
extraction of the data.

My macro is listed below:

Sub WheatstoneSolver()
'
' WheatstoneSolver Macro
'
' Keyboard Shortcut: Ctrl+l
'
    SolverReset
    SolverLoad LoadArea:="$A$12:$A$18"
    SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001,
AssumeLinear _
        :=False, StepThru:=False, Estimates:=1, Derivatives:=1,
SearchOption:=1, _
        IntTolerance:=5, Scaling:=False, Convergence:=0.0001,
AssumeNonNeg:=False
    SolverOk SetCell:="$D$6", MaxMinVal:=3, ValueOf:="0",
ByChange:="$B$1:$B$4"
    SolverSolve userFinish:=True
End Sub

Does anyone know how to access Excel macros or even access the Excel
Solver directly from within VEE?

Are there any free resources on the internet for using ActiveX?

Nick G.
SSI Technologies, Inc.
608-373-2234

---
You are currently subscribed to vrf as: decote@verizon.net
To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com".
To unsubscribe send a blank email to
"leave-vrf@it.lists.it.agilent.com".
To send messages to this mailing list, email "vrf@agilent.com".
If you need help with the mailing list send a message to
"owner-vrf@it.lists.it.agilent.com".

---
You are currently subscribed to vrf as: rsb@soco.agilent.com
To subscribe send a blank email to "join-vrf@it.lists.it.agilent.com".
To unsubscribe send a blank email to "leave-vrf@it.lists.it.agilent.com".
To send messages to this mailing list, email "vrf@agilent.com".
If you need help with the mailing list send a message to "owner-vrf@it.lists.it.agilent.com".
Like • Show 0 Likes0
Actions

vrf Using MS Excel Solver from a VEE 6.0 Program

Outcomes

Related Content

Recommended Content