Store, Compare, Store help

[pbaxter-disable]

Please see the attached for a description of what I've tried so far. Basically I need to:

1) Initialize a controlling array
2) Compare the values in the first column of each row of the initialized and new data, then store the controlling data to a new array
3) Repeat #2 each time new data is available

I'm using a scripted textbox to read in external data, which is the "new" information. Each time I select that file I want the comparison to happen and new, controlling data to be stored. At the end I'll export that data to an external file.

Since I don't know the number of times I need to run this process (depends on which external files I choose for my comparison) I can't just write that this loop happens "X" times.

My preference would be to run a script internal to the MC sheet, rather than dealing with an external Excel file (which isn't working properly anyway). Unfortunately this is beyond my programming skills so I'm asking for some help.

Thanks in advance!

Preston

[TomGutman]

My understanding is that what you need is to process an initially unkown set of files, a set specified by the user. Your need for storage in only because you are trying to loop at the sheet level, rather than within a single program (where local variable could be used. And that the desire for an external file for that storage is just due to failure to make internal storage work.

I would look at either confining the loop to a single program (perhaps using a multi-select list box in place of Mathcad's simplistic one, perhaps calling the selection list box from within a loop) or making the internal storage work.

What exactly is the problem with the Excel storage I posted? Does the sheet work as written (before you try to adapt it to your needs? If not, what happens? Previously you mentioned that you also had problems with the Excel write component, suggesting a more general problem interfacing with Excel. There could be an Excel version issue. The sheets I post have embedded Excel 2002. Later versions of Excel should be compatible, but who knows. Perhaps you should create your own Excel component (which will be for your version of Excel) and just copy the script. The internal storage component is really very similar to the file read/write components. The difference is mainly that the Excel object is predefined embedded rather than being created from a file. Theyh should work pretty much the same.
__________________
� � � � Tom Gutman

[pbaxter-disable]

On 11/6/2009 3:42:06 AM, Tom_Gutman wrote:
>My understanding is that what
>you need is to process an
>initially unkown set of files,
>a set specified by the user.
>Your need for storage in only
>because you are trying to loop
>at the sheet level, rather
>than within a single program
>(where local variable could be
>used. And that the desire for
>an external file for that
>storage is just due to failure
>to make internal storage work.
>

The external file is being used solely because I haven't figured out how to loop within the sheet. Correct.

>I would look at either
>confining the loop to a single
>program (perhaps using a
>multi-select list box in place
>of Mathcad's simplistic one,
>perhaps calling the selection
>list box from within a loop)
>or making the internal storage
>work.
>

Making internal storage work was my original intent. The selection list box for getting input data is already working to my satisfaction.

>What exactly is the problem
>with the Excel storage I
>posted? Does the sheet work
>as written (before you try to
>adapt it to your needs? If
>not, what happens? Previously
>you mentioned that you also
>had problems with the Excel
>write component, suggesting a
>more general problem
>interfacing with Excel. There
>could be an Excel version
>issue. The sheets I post have
>embedded Excel 2002. Later
>versions of Excel should be
>compatible, but who knows.
>Perhaps you should create your
>own Excel component (which
>will be for your version of
>Excel) and just copy the
>script. The internal storage
>component is really very
>similar to the file read/write
>components. The difference is
>mainly that the Excel object
>is predefined embedded rather
>than being created from a
>file. Theyh should work
>pretty much the same.

I found yesterday that things work much better on my system if I have Excel open. I mean just a blank, no-file-open session of Excel. It's only when I have Excel completely shut down that things sometimes go haywire. It could be because in the process of rebuilding my work computer recently, I ended up with Excel2003 and Excel2007 installed at the same time.

Anyway it looks like I can try out your internal storage app and see if I can't make it work for me.

Thanks for everyone's patience with my myriad questions.

Preston

[TomGutman]

Loops within a sheet are done within programs. This often means reworking worksheet assignments to functions, which can then be evaluated repeatedly in another program.

It seems to me that the selection box is one of the roots of the problem. You want the user to select multiple entries (files) from the list, but are using a component that only allows for a single selection. If you had multiple selections you would not need the static storage.

One solution is to do the operation in a program loop. Arrange for the program to call the select list in a loop (having redone the selection list as a function) and apply the selection to a local variable. Add an additional entry to the list (something like "*** done ***") that the user can select to indicate that he has selected all of the files he want to use. This terminates the loop and the program then returns the final result. That result can be written out using conventional tools.

Another way is a multi-selection box. The standard Windows list box has an option to allow multiple selections. It does require a bit more work to use that, but it is not all that difficult. As with the looped single selection approach, a dummy entry to indicate completion of the process can be used (in normal Windows usage a separate pushbutton is normally used for this purpose).
__________________
� � � � Tom Gutman

[pbaxter-disable]

The way the sheet is designed now, the user selects one file at a time from a list. The "loop" occurs when the next selection is made, but only one set of data is being compared to previously-stored data.

Preston

[TomGutman]

Yes, we'be been over that. But do note that this is not inherent in your underlying problem, but just a choice of implementation method. Other implementations are possible, implementations that keep the loop within a program, obviating the need for static storage at the worksheet level.
__________________
� � � � Tom Gutman

[pbaxter-disable]

Right - the idea is to develop a tool that when invoked, will initialize the data and then start running the loops. Current implementation requires separate tools for initialization and loop execution.

Richard's button may be the next step ... will have to spend some time understanding how it works and how to tweak it for my application.

Preston

[ptc-1368288]

>Right - the idea is to develop a tool that when invoked, will initialize the data and then start running the loops<<br> _________________

That's what the check box does
'if' check 1/0 = calcuate/NOcalcualte

jmG

[ptc-1368288]

In sciences/technology/engineering, wording is paramount. If the reader can't make a picture as it reads, nothing will come out. This time you have it ! and w/o Mathcad sheet. What about some NIST input/output card ? To a certain degree, Mathcad and NIST are convivial friends. It is so true that you will find the Mathcad DAEP "calibration of T/C's [Thermo Couple]" in their system.
So, Mathcad holds two data files as "sample & hold" Z-1, Z, you need that whatsoever for the Process Control Engineer/Optimizer "expert system", then spool back to the "User".

Preston, does it make sense (the diagram) ?

So, you have or will have the "Expert system", collabs will help script if possible, Preston & his client will be happy & Jean too.



Jean

[ptc-1368288]

"Expert systems" are not new to me. They started existing as soon as computers could crunch programmes. They were like "sealed", not with a candle stamp but like black box. They run quite advanced matrix stuff, optimizing algorithms ... Maybe in your project the matter is only "Control loops & stuff". If such it would be, then Mathcad is not the gear, rather a dedicated task to DCS [Distributed Control System]. The first 8 loop system on the market dates back 1971 "Micon" ( I know their designers). Micon was designed to be redundant from very small 8 loops up to large size (100's loops). The Micon system I was involved in design and commissioning, they were about unlimited in logic and analog functionalities ... just to control plants of about any sizes. Also, they could be slaved to any "expert maths crunchers".

jmG

[pbaxter-disable]

Apparently I haven't done a good job explaining things in words, so I've attached a diagram. :+)

All I'm looking for now is a way to always over-write an external Excel file, or create it if it doesn't exist, using a user-activated button or component.

Preston

[ptc-1368288]

On 11/6/2009 2:58:28 PM, pbaxter wrote:
...
>All I'm looking for now is a
>way to always over-write an
>external Excel file, or create
>it if it doesn't exist, using
>a user-activated button or
>component.
>
>Preston
______________________________


Mathcad programme READPRN reads *.prn, *,dat, *.txt
It reads as per the user spec's
1. the all lot serially
2. on a range of files
3. on a per "forloop" list select
When Mathcad spools to file, it overwrites.
When no file exists, it creates one.

The spool to file on the same pattern shouldn't be a problem to program. I don't have it as a working tool. If your end user, zillions km away has only Excel and if can't read/save as either of these 3 Mathcad types of files, he his the guy with problem. In fact, Excel does not have either of these three types of files. So, you need the OUT<==>IN converter. What is the use of a push button component as well as a click one selection list box or range select list box if your programme has already done that. OK, I understand that if you spool-IN file 29...43, you may like having that "magic list box" especially if the directory has been wisely constructed with a file descriptor.

That file type converter, isn't possible to have it resident in Excel and so not bother Mathcad wherein you will just have the "magic list box", eventually not needed and Mathcad doing his own stuff only ?

jmG

[pbaxter-disable]

Good point that I didn't need to be "locked in" on Excel. One of the text formats would have been sufficient, more universal and there is existing functionality for writing to them programmatically.

With recent input from others, I now have a working method for keeping the data internal to Mathcad so read/write with an external data file of any format is no longer required.

Preston

[RichardJ]

On 11/5/2009 10:58:19 PM, pbaxter wrote:

>What I still need is a button
>than when pressed, writes data
>to a given filename. All the
>examples I've found in the
>Collab will only do this if
>the file already exists. Isn't
>there a way to CreateObject if
>the GetObject part of the
>script returns an error (ie if
>the file doesn't currently
>exist)?

The best way is to use the FileExists method of the Scripting.FileSystem object to check if the Excel file exists, then either open it or create it depending on the result. Do you still need this though, or are you now just storing the results in the embedded Excel sheet?

Richard

[pbaxter-disable]

On 11/7/2009 8:23:13 AM, rijackson wrote:
>On 11/5/2009 10:58:19 PM, pbaxter wrote:
>
>>What I still need is a button
>>than when pressed, writes data
>>to a given filename. All the
>>examples I've found in the
>>Collab will only do this if
>>the file already exists. Isn't
>>there a way to CreateObject if
>>the GetObject part of the
>>script returns an error (ie if
>>the file doesn't currently
>>exist)?
>
>The best way is to use the FileExists
>method of the Scripting.FileSystem
>object to check if the Excel file
>exists, then either open it or create it
>depending on the result. Do you still
>need this though, or are you now just
>storing the results in the embedded
>Excel sheet?
>
>Richard

I'm still interested in this functionality for future uses.

Thanks,
Preston