Eng-Tips is the largest engineering community on the Internet
Intelligent Work Forums for Engineering Professionals
-
Congratulations waross on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
combining excel and mathcad 3
- Thread starter sjjustine
- Start date
-
1
- #2
-
1
- #3
Mattman206
Electrical
How exactly do you want to interface Mathcad with Excel?
You can:
-Import/Export arrays and matrix data to/from an Excel file via the Insert>Component...>File Read or Write.
-Insert an Excel object into the current Mathcad worksheet.
Insert>Component>Excel. You define input/output cells and can use all of the functions that Excel provides.
-Insert 3D Mathcad graphs into an Excel sheet. You need to download the Mathcad Add-In for Microsoft Excel at (Direct link This is a very nice feature and I have used it quite a bit.
If anyone else knows other ways to interface Mathcad and Excel please comment on this!
Hope this helps!
Matt
You can:
-Import/Export arrays and matrix data to/from an Excel file via the Insert>Component...>File Read or Write.
-Insert an Excel object into the current Mathcad worksheet.
Insert>Component>Excel. You define input/output cells and can use all of the functions that Excel provides.
-Insert 3D Mathcad graphs into an Excel sheet. You need to download the Mathcad Add-In for Microsoft Excel at (Direct link This is a very nice feature and I have used it quite a bit.
If anyone else knows other ways to interface Mathcad and Excel please comment on this!
Hope this helps!
Matt
- Thread starter
- #4
Mattman206
Electrical
Ok, simple enough.
In Mathcad, just go to Insert>Component... and select "File Read or Write" in the dialog box.
Then, make sure the "Read from a file" radio button is highlighted on the next screen.
Under the next screen, select "Excel" from the File Type drop-down, choose your saved Excel file with the Browse... button.
Click Ok and Mathcad inserts a File Read component into your worksheet. See how the component is on the right side of the := assigment operator? Now all you do is tell Mathcad what variable to place the data from your Excel sheet into. Just for simplicity, type in "A" into the variable placeholder. If the component turns red with an error you should go back and make sure that the file you specified actually exists.
Then type "A=" to dump what is in A to the screen. You should see all of the data from your Excel file in the A matrix!
From there, you can access the data in A just like you would any other matrix.
For example, if you want to store only the 3rd column to B, you'd type:
B:A(Ctrl-6)2
(2 is the column number, because the columns are numbered 0,1,2, etc.) Ctrl-6 makes a Matrix Column operator and can also be found in the Matrix toolbar
If you want to store only the 3rd row to C, you'd type:
C:A(Ctrl-1)(Ctrl-6)2
Ctrl-1 is the Matrix transpose operator flips the matrix along its diagonal so all the rows become columns and all the columns become rows. It shows up as a superscript T. The the Matrix Column extracts off the 3rd column (formerly the 3rd row befor the transpose) and puts it in C.
If you just want a single number from A, just type:
A[r,c=
Where r and c are the row and column indeces of the number you want.
Now, lets say that you have a formula f(x,y) and your A matrix contains two columns corresponding of values of x and y, and you want to evaluate this function for all of values of x and y in your Excel sheet.
Make a range variable that starts at 0 and goes until how many rows you have. Remember that the value of the index is always one less since it starts at 0.
i:0,1;rows(A)-1
As our test function let's find the distance of an (x,y) pair to the origin.
f(x,y):\x^2 +y^2
(Translation: f(x,y)=sqrt(x^2+y^2)
We'll make a column vector called d which contains the distance of each (x,y) pair
x:A(Ctrl-6)0
y:A(Ctrl-6)1
d[i:f(x[i,y[i)
And there you go! First we extracted the columns of A into x and y, and then we used the subscript operator with a range variable to store the results of the function. Type "d=" to see the answers.
Hope this has helps!
-Matt
In Mathcad, just go to Insert>Component... and select "File Read or Write" in the dialog box.
Then, make sure the "Read from a file" radio button is highlighted on the next screen.
Under the next screen, select "Excel" from the File Type drop-down, choose your saved Excel file with the Browse... button.
Click Ok and Mathcad inserts a File Read component into your worksheet. See how the component is on the right side of the := assigment operator? Now all you do is tell Mathcad what variable to place the data from your Excel sheet into. Just for simplicity, type in "A" into the variable placeholder. If the component turns red with an error you should go back and make sure that the file you specified actually exists.
Then type "A=" to dump what is in A to the screen. You should see all of the data from your Excel file in the A matrix!
From there, you can access the data in A just like you would any other matrix.
For example, if you want to store only the 3rd column to B, you'd type:
B:A(Ctrl-6)2
(2 is the column number, because the columns are numbered 0,1,2, etc.) Ctrl-6 makes a Matrix Column operator and can also be found in the Matrix toolbar
If you want to store only the 3rd row to C, you'd type:
C:A(Ctrl-1)(Ctrl-6)2
Ctrl-1 is the Matrix transpose operator flips the matrix along its diagonal so all the rows become columns and all the columns become rows. It shows up as a superscript T. The the Matrix Column extracts off the 3rd column (formerly the 3rd row befor the transpose) and puts it in C.
If you just want a single number from A, just type:
A[r,c=
Where r and c are the row and column indeces of the number you want.
Now, lets say that you have a formula f(x,y) and your A matrix contains two columns corresponding of values of x and y, and you want to evaluate this function for all of values of x and y in your Excel sheet.
Make a range variable that starts at 0 and goes until how many rows you have. Remember that the value of the index is always one less since it starts at 0.
i:0,1;rows(A)-1
As our test function let's find the distance of an (x,y) pair to the origin.
f(x,y):\x^2 +y^2
(Translation: f(x,y)=sqrt(x^2+y^2)
We'll make a column vector called d which contains the distance of each (x,y) pair
x:A(Ctrl-6)0
y:A(Ctrl-6)1
d[i:f(x[i,y[i)
And there you go! First we extracted the columns of A into x and y, and then we used the subscript operator with a range variable to store the results of the function. Type "d=" to see the answers.
Hope this has helps!
-Matt
- Thread starter
- #6
I have managed to open the file, but now I have problem to take values from the column and apply it into my equation. I tried to do it occording to the exemples u send, but I donot get it right.
For exemple:
I want to take all the values in a column(B) and apply it in my equation which is
molesx:=constants*(columnB) How to I do that?
another question: Can I more than one column with defferent values into the same equation and get different values molesx for each column. For ex.
molesx:=constants*(column1,column2....)
get molesx for column1 moles for column2 and so on
For exemple:
I want to take all the values in a column(B) and apply it in my equation which is
molesx:=constants*(columnB) How to I do that?
another question: Can I more than one column with defferent values into the same equation and get different values molesx for each column. For ex.
molesx:=constants*(column1,column2....)
get molesx for column1 moles for column2 and so on
Mattman206
Electrical
Hmm, I tried to type the Mathcad lines just as you would type them in Mathcad. So when I typed ":" that would be a ":=" in Mathcad, etc.
Try going to View>Toolbars>Matrix to get access to all of the matrix functions. When I said (Ctrl-6) that is the M^<> thingee. The (Ctrl-1) is the M^T button. I just told you the keystroke shortcuts instead of how to get them from the toolbar.
(I know you probably know this, but just to cover all the bases, when I said (Ctrl-1) that means to hold down the Ctrl key and press the 1 key at the same time.)
As for the matrix operators, just check out the help files on them. Look up "matrix operations".
Matt
Try going to View>Toolbars>Matrix to get access to all of the matrix functions. When I said (Ctrl-6) that is the M^<> thingee. The (Ctrl-1) is the M^T button. I just told you the keystroke shortcuts instead of how to get them from the toolbar.
(I know you probably know this, but just to cover all the bases, when I said (Ctrl-1) that means to hold down the Ctrl key and press the 1 key at the same time.)As for the matrix operators, just check out the help files on them. Look up "matrix operations".
Matt
Wittenborn
Aerospace
Mattman,
Brilliant! I just came up on the same exact problem, and wondered how I was going to figure this one out!
Many thanks for your advice!
Regards,
Grant
Aerospace Engineer
Brilliant! I just came up on the same exact problem, and wondered how I was going to figure this one out!
Many thanks for your advice!
Regards,
Grant
Aerospace Engineer
There was, once upon a time, a Mathcad add-in for Excel. I think it was intended for Mathcad 2000, but I am using it successfully (so far) with Mathcad 11. It might still be on the mathsoft website somewhere, or maybe you could track down a copy via the collaboratory? It allows you to place mathcad regions right inside a cell in Mathcad. Quite good as a party trick, but I can't honestly say I've found it any more useful than the methods described above, especially as nobody else can use your spreadsheets anyway unless they also have mathcad.
Bung
Life is non-linear...
Bung
Life is non-linear...
There is a version of the Mathcad component for Excel for v11. It's on the download page:
TTFN
TTFN
Boothby171
Mechanical
I'm about to try it out, but I've got a slightly different issue, and I'm not sure oif the above solutions will solve it.
I've already got an Excel spreadsheet object sitting in MathCAD V.11. I need to feed it variable data from the surrounding MathCAD sheet, and later on pull data from it back into the MathCAD sheet.
Unless the above stuff works (and due to the apparent need for an external file reference, I think it may not), what does?
Thanks in advance!
--Steve Sywak
I've already got an Excel spreadsheet object sitting in MathCAD V.11. I need to feed it variable data from the surrounding MathCAD sheet, and later on pull data from it back into the MathCAD sheet.
Unless the above stuff works (and due to the apparent need for an external file reference, I think it may not), what does?
Thanks in advance!
--Steve Sywak
I'm confused. The embedded Excel object does NOT need an external file reference. A use of the that particular object is to simply store data for access by the Mathcad sheet. In others, there may be some functionality that's available in Excel that is used to process data and then ship the results back to Mathcad.
When you insert the Excel object, it's normally defaulted to 1 input variable and 1 output variable.
TTFN
When you insert the Excel object, it's normally defaulted to 1 input variable and 1 output variable.
TTFN
Boothby171
Mechanical
IRStuff,
Thanks for the quick response.
I'm using "someone else's" MathCAD file, so how it was started up is out of my control.
1) Yes, the embedded Excel object is NOT an external reference. If it were, it would probably make my life a whole lot easier. It does contain equations, and so is far more useful than just a means to store an array of data. Being able to do this actually makes MathCAD a far more appealing package.
2) What I need to do is something like this
A:=15.5
PUSH(Embedded_Spreadsheet_X,Row,Column)=A
D=PULL(Embedded_Spreadsheet_X,Row,Column)
Where I can push a variable into a cell in the embedded spreadsheet, and pull a result back into a variable for MathCAD (proper) to use.
And I want to keep the embedded spreadsheet present in the MathCAD sheet so that both when I save it, and when I print it out for my project records, the data is all together.
Can it be done?
Thanks for the quick response.
I'm using "someone else's" MathCAD file, so how it was started up is out of my control.
1) Yes, the embedded Excel object is NOT an external reference. If it were, it would probably make my life a whole lot easier. It does contain equations, and so is far more useful than just a means to store an array of data. Being able to do this actually makes MathCAD a far more appealing package.
2) What I need to do is something like this
A:=15.5
PUSH(Embedded_Spreadsheet_X,Row,Column)=A
D=PULL(Embedded_Spreadsheet_X,Row,Column)
Where I can push a variable into a cell in the embedded spreadsheet, and pull a result back into a variable for MathCAD (proper) to use.
And I want to keep the embedded spreadsheet present in the MathCAD sheet so that both when I save it, and when I print it out for my project records, the data is all together.
Can it be done?
But that's exactly what the embedded component does.
If you put the edit cursor on the Excel table display itself and right-click, you should get a context menu with options to add/delete input/output variables. If you select "Properties", you should get the current configuration of the component and you can adjust accordingly.
TTFN
If you put the edit cursor on the Excel table display itself and right-click, you should get a context menu with options to add/delete input/output variables. If you select "Properties", you should get the current configuration of the component and you can adjust accordingly.
TTFN
Boothby171
Mechanical
IR,
I tried that. No such choices come up with the right-click.
Under "Properties," I get two tabs: Display and Protect. I've turned Protect=Off (not that it mattered). Under Display, I have "Highlight Region" (off), "Show Border" (off) and "Tag". I've assigned a name to "Tag", but it doesn't change anything.
Edit and Open (under "Worksheet Options") are the only other right-click choices, and they do just that--allow me to edit the spreadsheet within MathCAD or Excel, respectively. Neither approach lets me add or modify I/O control.
I'm running MathCAD 11 SP2a (it didn't work under SP1, either).
I appreciate your free help. Feel equally free to give up on this any time you like. I don't think there's a solution.
I tried that. No such choices come up with the right-click.
Under "Properties," I get two tabs: Display and Protect. I've turned Protect=Off (not that it mattered). Under Display, I have "Highlight Region" (off), "Show Border" (off) and "Tag". I've assigned a name to "Tag", but it doesn't change anything.
Edit and Open (under "Worksheet Options") are the only other right-click choices, and they do just that--allow me to edit the spreadsheet within MathCAD or Excel, respectively. Neither approach lets me add or modify I/O control.
I'm running MathCAD 11 SP2a (it didn't work under SP1, either).
I appreciate your free help. Feel equally free to give up on this any time you like. I don't think there's a solution.
Two possibilities:
>> You need to select only the table and not the entire expression containing the table. The context menu only pops up if the Excel table alone is selected.
>> Your installation is mucked up. You can check this by trying in insert a nex Excel component. If when you do that and it doesn't ask questions about creating a new one or from a file, your installation is honked up. If this does occur, and you've had an older version of Mathcad, you should have a program called Regtool in your Mathcad folder. If so, un-register and re-register all the components.
The Excel component works as I described it in a fully functional installation. I have the same configuration and checked it before I posted my previous response.
TTFN
>> You need to select only the table and not the entire expression containing the table. The context menu only pops up if the Excel table alone is selected.
>> Your installation is mucked up. You can check this by trying in insert a nex Excel component. If when you do that and it doesn't ask questions about creating a new one or from a file, your installation is honked up. If this does occur, and you've had an older version of Mathcad, you should have a program called Regtool in your Mathcad folder. If so, un-register and re-register all the components.
The Excel component works as I described it in a fully functional installation. I have the same configuration and checked it before I posted my previous response.
TTFN
Boothby171
Mechanical
Thanks, IR.
Apparently, the guy who built the MathCAD document before me got the Excel table insertion/inclusion all wrong. I can properly insert Excel tables in new files, as well as in the old file.
Maybe a little bit of judicial cut-'n'-paste and I can make it all right again.
Apparently, the guy who built the MathCAD document before me got the Excel table insertion/inclusion all wrong. I can properly insert Excel tables in new files, as well as in the old file.
Maybe a little bit of judicial cut-'n'-paste and I can make it all right again.
-
1
- #18
I have read all the posts above about getting mathcad calculations into an excel file. I have version 11 and am using the excel component as suggested above. I went to the mathsoft site and saw the suggestion to use the "save as" command to get the data into an current excel spreadsheet. It works sometimes. Here is my case. The first time I tried it this morning, it worked just fine. However, the file it writes to comes up as a hidden file in excel and it has to be "unhidden". After that, I am not able to export the same way.
The variable is updated in the program and it is in the output variable location. This process seems very simple but I seem to have missed something.
JimBlech
The variable is updated in the program and it is in the output variable location. This process seems very simple but I seem to have missed something.
JimBlech
Boothby171
Mechanical
All,
Thanks for your help on this. I've since given up and moved on.
I think this features scores high on the "Not Ready for Prime Time" list--it sounds like it causes more frustration than it might be worth.
Thanks for your help on this. I've since given up and moved on.
I think this features scores high on the "Not Ready for Prime Time" list--it sounds like it causes more frustration than it might be worth.
I have worked on this problem some more yesterday and, quite by accident, found the following:
When working with large file transfers, it takes some time for the save to take place. While this is occuring, there is not indication to the screen that this is happening. If you go to excel before the save is done, you lose the new information.
I go to the taskbar and look at CPU usage. When it settles down, I open the excel file (from excel). This is very cumbersome and not very intuitive but it works.
When working with large file transfers, it takes some time for the save to take place. While this is occuring, there is not indication to the screen that this is happening. If you go to excel before the save is done, you lose the new information.
I go to the taskbar and look at CPU usage. When it settles down, I open the excel file (from excel). This is very cumbersome and not very intuitive but it works.
- Status
Similar threads
- Locked
- Question