Running a MathCAD sheet multiple times with different variable inputs from an excel spreadsheet

[SM_6465178]

Hi All, 

We have recently carried out some testing on a system which has generated quite a bit of data on a spreadsheet. 

A calculation was set up to prove the original operation, but since there is now data, our client would it processed. Is there a way to obtain data from an excel spread sheet (one column), put a single value (one cell of the column), process via mathcad calculation and then export the cell to another spread sheet with the result variable desired?

[DJNewman]

Yes, please read up on the readexcel and writeexcel functions. You can also embed an Excel Component which could be useful, but from what you've described, read/write seem to be the way to go.

https://www.mathcad.com/en/blogs/using-excel-read-write-component

https://support.ptc.com/help/mathcad/r10.0/en/index.html#page/PTC_Mathcad_Help/excel_data_files.html

https://support.ptc.com/help/mathcad/r10.0/en/index.html#page/PTC_Mathcad_Help/example_reading_and_writing_data_files.html#

https://support.ptc.com/help/mathcad/r10.0/en/index.html#page/PTC_Mathcad_Help/about_excel_component.html

[SM_6465178]

This is part of the solution, but not the critical part of

it. I need to run it by putting in a variable from the spreadsheet where the top red mark is, add it to a vector and process it to get a number at the bottom.

So while I can use read excel to get one variable through to get 1 answer, I need it to return to the top, get the next variable and run that one through as well, obviously continuing till I run out of data.

[DJNewman]

Can you structure this process with a program with a loop?

(Sounds like you may or may not want it to be recursive too if I'm understanding it right, which I might not be...)

I get the impression you don't want to share the worksheet because it has your client's details on it and you can't make that public, so I can't offer much more specific suggestions.

[SM_6465178]

Hi, 

I'll try and post it here. The website through a hissy fit the last time I try to post it.. I'm having to modify it slightly. But to give you something that would work, if you look at the output from the test rig, I want to import the angle into the alpha check variable and the two static ram forces (the last two columns shown) into FLA and FLB respectively. For where I'm currently at in the worksheet, if it could export the magnitude of R4A and R4B so I can see how it works if it's possible (or some basic steps on how to go about it, I imagine if I can get this to work, we would use something similar quite often)

[SM_6465178]

I did add some extra lines. If I could automate the read and write excel loops, I think that would work

[anursingh]

Hi @SM_6465178,

I wanted to follow up with you on your post. It seems that your issue has been answered. 
If so, please mark the appropriate reply as the Accepted Solution. 
 

Thanks,
Anurag
 

[SM_6465178]

Hi Anurag,

While I have seen some really awesome ways in which we can use MathCAD with this thread, the topic is still not answered. And maybe its an external thing that needs to happen. I think with the last MathCAD sheet I supplied, it probably streamlines the process.

So what I would like to do, is read the data from the cell into MathCAD, then process it (via some solve blocks and other equations that may lend themselves to programming (if they do, would anyone have a really simple example) and then write the result in the cell. Then loop and start the process again with the next row down and continuing until the data is processed.

[Werner_E]

Why don't you read in the whole data column from Excel in a Prime vector in one go, process each element of that vector in Prime (it would to turn your calculations into functions), collect the results in a new vector which at the end is written back to the Excel sheet.

[SM_6465178]

Hi Werner,

I'm just wondering if you would be able to take a quick look at the MathCAD sheet I posted on May 22nd to see if I could apply this method to achieve  what you are thinking. If it does, then it has just made MathCAD much more powerful than I thought!

[Werner_E · Accepted solution]

Changing all calculations in an already existing worksheet into functions of some input variables can be quite cumbersome and also may impair the readability of the sheet.

I made a try with your sheet and stopped where your sheet throws an error anyway (as of F4 being undefined).

See the changes and comments (all highlighted in yellow) in the attached sheet.

At the end I show how to read in the data from the Excel sheet, use the functions defined and write back the data to Excel.

Hope it helps - Good luck!

[SM_6465178]

Hi Werner, 

That was exactly what I wanted. It doesn't help that I sent the wrong MathCAD prime sheet in which kind of shows where I wanted it read in but you figured it out.
The force F4 is I guess the inverse sum of the R4B and R4A but I assume it would be easy enough to create that function. I'll give it a go and see how that pans out.

Thank you for the guidance, it has been really helpful.

[ttokoro]

Using READEXCEL and WRITEEXCEL I made tetrahedron plots. At first READEXCEL read original tetrahedron 1.xlsx. Then made and write 2.xlsx file. Thereafter program continue the sequence. 

You must make unique names for each excel file and read and write it sequencially. 

[JCahill1]

This is another thing that could be done with original Mathcad but, apparently can't be done with Prime. You used to be able to insert a Mathcad component within Excel and use an Excel macro to step through the rows reading input data from excel, calculating the result with Mathcad, and returning the result to Excel. I used it when I had multiple (more than 2) input parameters to explore. At least on my setup, Excel lists "Mathcad Worksheet" under Install/ Object, but selecting that brings up the message "Cannot start the source application for this object." I presume that it does not work with Prime.