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

[ptc-1368288]

Not easy to understand. It looks you want to store some "controlling" data to Excel, then apply a unidirectional test in Excel for the person far away to be warned the pilot has changed direction. You can probably do bidirectional as illustrated. The attached does not consider a range threshold, just 1/1 logical test.

Jean

[pbaxter-disable]

OK the method using file input/output plus Ctrl-F9 seems to work. If you want to test this, you'll have to create a blank "Test.xls" file in your C:\ directory.

My preference is still to keep all this analysis contained within the Mathcad file if at all possible. Is this a simple programming matter?

Thanks,
Preston

[TomGutman]

What is the overall process like? At what point do you know all the data files that will be used? Do you need intermediate results (results from just some of the data files)? How are the data files identified? Do you have to list then explicitly, or is there some rule (presumably based on the names) that can identify them?

Given a set of data files (either as an entered list or generated by applying a filter to a directory listing) you can easily enough loop through that set of data files and produce a result for that set.

Oh, what problems are you having with my Excel write component? In your original sheet you have a problem in that you are using invalid range names.
__________________
� � � � Tom Gutman

[pbaxter-disable]

On 10/30/2009 12:53:17 PM, Tom_Gutman wrote:
>What is the overall process
>like? At what point do you
>know all the data files that
>will be used? Do you need
>intermediate results (results
>from just some of the data
>files)? How are the data
>files identified? Do you have
>to list then explicitly, or is
>there some rule (presumably
>based on the names) that can
>identify them?

Given a set of
>data files (either as an
>entered list or generated by
>applying a filter to a
>directory listing) you can
>easily enough loop through
>that set of data files and
>produce a result for that
>set.

Because you asked ...

I'm developing this tool to process a series of output files for each of hundreds of structures. Here's the workflow:

1) I run a number of analyses and get output files. The "number" can be one, two, or twenty for a particular structure.
2) Using some modified components from this Collab, the user "browses" through the file structure to the directory containing the output files.
3) Using another component that filters based on file extension, the user selects one of the output files which is then read into Mathcad.
4) Various lookup functions extract several pieces of information from that particular output file.
5) A comparison is made with the intialized data or previously-stored controlling data for that same structure, and the new controlling data is stored.
6) The user returns to the list of output files, selects another one, and the comparison routine happens again.
7) The user does NOT necessarily select all files of a particular type. If there are 12 output files, perhaps only 2 or 4 or maybe all 12 are appropriate for comparison. Thus the user must select, one by one, the files to be compared.
😎 The controlling data will be pushed to a summary spreadsheet that will ultimately be delivered to the client.

Right now I'm at a point where I can use an external spreadsheet to "contain" the controlling data. It is read into Mathcad when the user selects the next output file from the list, and recalculation of the sheet writes out the new controlling data. My hope is that there's a relatively simple scripting method to accomplish the same thing, but without having to use the external file.

Oh, what problems are
>you having with my Excel write
>component? In your original
>sheet you have a problem in
>that you are using invalid
>range
>names.

The component seems to run successfully only once per Mathcad session. When the component reports an error, I have to close and re-open the file. (The same thing sometimes happens with the push-button components that work with Excel.) I haven't done enough testing to be able to properly explain WHAT causes the issue.

I'm not sure what is wrong with the range names in the example file ... it seems to be working OK for me.

Thanks,
Preston

[PhilipOakley]

Preston wrote:
The component seems to run successfully only once
per Mathcad session. When the component reports an
error, I have to close and re-open the file. (The
same thing sometimes happens with the push-button
components that work with Excel.) I haven't done
enough testing to be able to properly explain WHAT
causes the issue.

Yes V14 does that. It is really annoying. It wasn't
like that in V11 and for VBscripts means that you
must include all types of error detection to catch
them before it creates that error flag, which
doesn't clear.
Not much fun.


Philip Oakley

[TomGutman]

Does the user need to see the results of using a particular data file before deciding on what additional file(s) to use, or can he select all of the files he wants to start with?

If he can know beforehand all the files he will want I would look at building a scripted component using a multi-select list box to present the user with a list of all the potential data files and have him select the ones he wants, and then process them all in a loop.

If he needs to see the results of each file before proceding, I would look at building a scripted component using an Excel data sheet to hold the data. One should be able do have such a component defining a function that can read and write parts of the Excel sheet, and that can be used as the storage medium.

If I read the original sheet correctly, you are using that second column, with numbers, as the range name for the write routine. I have not been able to get the first part of the sheet, creating the original file, working, so have not been able to test well. BTW, I don't generally like creating files in the root directory -- tends to make a mess of things. For what you are doing I would suggest using the c:\temp directory.

Errors executing a script effectively disable that script. You can recover from that by editing the script -- no need to completely reload the work sheet. Editing a script (even if you make no changes) result in a complete reinitialization of the scripted component.
__________________
� � � � Tom Gutman

[pbaxter-disable]

Sorry for the delayed response ... had the joy of rebuilding my computer system.

I am using a scripted component to present a list of potential files to the user. As each file is selected from the list box, the data is then read in, processed, then should be compared with previously-stored data and the new controlling data stored. The user does not need to see the output of each file first.

Thanks,
Preston

[TomGutman]

Try using a MS list box, with the multi-select option, as a scriptable object. That should allow the user to select all the files before any processing is done. With a complete list of files it should be easy to iterate through the files within a Mathcad program.
__________________
� � � � Tom Gutman

[TomGutman]

Here's an Excel storage component. It's an embedded Excel sheet where you can read or write data using a simple function.
__________________
� � � � Tom Gutman

[pbaxter-disable]

This function doesn't seem to work in v14. I get an error message that states "Error HRESULTE_FAIL has been returned from a call to a COM component." This is simliar to the error I get occasionally from your WriteExcel component, when trying to access an external file repeatedly.

Preston

[TomGutman]

The sheet works in MC14. That's where I developed it, and I just opened the uploaded file in MC14 and it works.

You may have an issue with Excel, either the version or perhaps a problem with the installation. I am using Excel 2002. Try doing a repair of the excel installation and see if that helps.
__________________
� � � � Tom Gutman

[pbaxter-disable]

Tom,

Looks like now that I have my Excel issues ironed out, this component could work very well. All I need is a way to intialize the data only when the user desires.

Can a button be used to enable and disable a particular region?

Preston

[pbaxter-disable]

OK this seems to be working now. Suggestions for more efficient processing are still welcome.

In the actual file, new data is read in when the user selects a data file name from a list component. Some comparisons are done, and the new data is compared to generate what is currently in the "otherwise" line of the program. That data is then written back to the component.

Thanks,
Preston

[TomGutman]

Don't invoke the recalculate method in the EXEC routine. The purpose of the recalculate method is to cause the EXEC routine to be executed. The use of it within the EXEC routine is logically erroneous. Fortunately here it seems to be being ignored. With some implementations (check Mathcad versions it could conceivably result in an endless loop.
__________________
� � � � Tom Gutman

[pbaxter-disable]

On 11/7/2009 1:03:22 AM, Tom_Gutman wrote:
>Don't invoke the recalculate
>method in the EXEC routine.

I think that was a leftover from when I was using an external file. Without recalculating the entire sheet, the new data wouldn't be read in correctly. Now I think the auto calculation should run the internal script.

Thanks for this tip.

Preston

[RichardJ]

Here's a better button. If your button is clicked the initialization is turned on, but the user needs to click a second time to turn it off. With this button, the second click is not necessary.

Richard

[AlvaroDíaz]

On 11/7/2009 8:17:33 AM, rijackson wrote:
>Here's a better button ...

Can't be done with a CheckBox?

Regards. Alvaro

[pbaxter-disable]

Sure - I think the check box does the same thing as my push button approach?

Preston

[RichardJ]

On 11/7/2009 6:32:46 PM, adiaz wrote:
>On 11/7/2009 8:17:33 AM, rijackson
>wrote:
>>Here's a better button ...
>
>Can't be done with a CheckBox?

Your checkbox behaves the same as a standard button. If you check it then calculation is turned on. If you then change the argument to the function it recalculates the answer immediately. If you want to turn the calculation off you need to manually uncheck it. The button I provided enables calculation, and then disables it again via a function call. So if you then change the argument to the function it is not automatically recalculated.

Richard

[AlvaroDíaz]

On 11/8/2009 7:01:11 AM, rijackson wrote:
>The button ...

Thanks for explain the functionallity of the button, I suspect that need to try them with a more rallistic case to see why the collab needs that

>the function it is not automatically recalculated.

with the flux diagram provided, but it's obviously that the check box don't do this.

Regards. Alvaro.

[ptc-1368288]

>the function it is not automatically recalculated.<<br> ______________________

NO, and for safety reasons. Lot of users use Microsoft applications, directly or indirectly, and for public safety the "confirm action" is a minimal MUST !

jmG

[ptc-1368288]

On 11/8/2009 4:18:16 PM, jmG wrote:
>>the function it is not automatically recalculated.<<br> >______________________
>
>NO, and for safety reasons.
>Lot of users use Microsoft
>applications, directly or
>indirectly, and for public
>safety the "confirm action" is
>a minimal MUST !
>
>jmG
_______________________________

What I meant is:
Maths have no "animus", they are dead like dead stone. The animus, i.e: the intention must either come from a test or from the finger. Even the FRAME batch calculation that APPENDPRN needs the recorder. In DCS, the "normal animus" is the loop scan. The "extraordinary animus" is generate by summing and testing the list of priorities alarm. Special alarms are detected by the protocol, which protocol embeds the "priority animus". Mathcad work sheet being essentially maths and that maths are dead, no escape to the animus.

jmG

[pbaxter-disable]

Using a combination of list boxes, Write-to-Excel scripts and worksheet calculations I have my process working. Almost ready for distribution ...

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)? Or is there a way to always create a new Excel file, over-writing if there was already an existing file?

I need this in a push-button sort of component because it must NOT happen every time the file is recalculated, but only when the user specifically needs to re-initialize. That's why I can't use the built-in File|Output feature.

Yes, I know I just need to take the plunge and learn VB for myself ... I have an 850-page textbook on my desk that I plan to learn from in my "spare time." Whenever that is.

Thanks again in advance,
Preston

[AlvaroDíaz]

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.

Creating a file from nothing with code could be hard.

Having a blank file like a template, stored somewhere and copying/renaming helps?

Regards. Alvaro.

[TomGutman]

Generally you have to open the application and use the equivalent of file/new. Then you use save as to actually create the file.

It's not clear why you need an external file -- what is wrong with an embedded Excel sheet?

Mathcad is capable of writing to Excel files and can create a new file.
__________________
� � � � Tom Gutman

[pbaxter-disable]

On 11/6/2009 1:35:57 AM, Tom_Gutman wrote:
>Generally you have to open the
>application and use the
>equivalent of file/new. Then
>you use save as to actually
>create the file.

Sounds like that would work fine. Is there a Mathcad scripting reference that contains sample code for doing stuff like that?

>It's not clear why you need an
>external file -- what is wrong
>with an embedded Excel sheet?

I was never able to make the workflow work using an embedded component. I need to read in external data, compare with previously-stored data, then store new controlling data, then start the loop again. I worked for awhile trying to find a method of doing this using components, but I was unable. Haven't found a scripting way to do it. Using the external file is the only thing that has worked for me.

>Mathcad is capable of writing
>to Excel files and can create
>a new file.

I use the FILE|Output feature regularly. However, I need the capability to create a new file only when the user directs (not every time the file recalculates), which doesn't work using the built-in function. I use your WriteExcel script, but that one only writes to an existing file. I'm not far enough through my VB textbook yet to figure this one out on my own.

"Brute Force" is king for now ...

Preston

[StuartBruff]

On 11/6/2009 1:52:25 AM, pbaxter wrote:
>On 11/6/2009 1:35:57 AM, Tom_Gutman
>wrote:
>>Generally you have to open the
>>application and use the
>>equivalent of file/new. Then
>>you use save as to actually
>>create the file.
>
>Sounds like that would work fine. Is
>there a Mathcad scripting reference that
>contains sample code for doing stuff
>like that?

Sorry, I haven't looked through this thread, but would the file routines in the worksheet in http://collab.mathsoft.com/read?129630,20 be of any use?

Stuart

[pbaxter-disable]

Honestly I gave up following that thread way before it got to message 56. I see that there are some useful file in/output functions. Maybe one can be tweaked to write out new Excel files.

Thanks
Preston

[ptc-1368288]

On 11/6/2009 12:17:58 PM, pbaxter wrote:
>Honestly I gave up following
>that thread way before it got
>to message 56. I see that
>there are some useful file
>in/output functions. Maybe one
>can be tweaked to write out
>new Excel files.
>
>Thanks
>Preston
______________________________

Now, you are much less clear. You have to have either a scanning functionality or a logical responsive test if you want Mathcad to read the file and the receiver have same after you spool back to the user file. That's what is a "handshake protocol" ... typical and universal HP-HB working in all industrial systems. How to implement in Mathcad and as a scripted component, a question for Watson or Richard. You probably need only the NIST interface card. I thought the diagram posted above would help but it does not seem so.

jmG