Export all variables to Excel

Hi Arne,

Hope this helps? Have a detailed description of various methods. Have a sample Mathcad file and resulting Excel readable file using one of the methods.

There are thousands of output variables in the resulting complex file. Excel reads the file as text import with spaces as the chosen delimiter. Other target software STRAND 7 can also use the file directly.

Depends on what you are trying to achieve and how repeatable you want it to be. Many ways to do this. Here are some. One is obscure. Probably others exist.

>>>>>>>>>>>>>>>>>>>>>>

Subscript the variable so the 1000 entries end up in the one place in one variable. You can almost always structure a work sheet so 1000 answers are in the one variable.

You can use stack() and augment() functions to help do this. Look these functions up in help.

I often use a matrix with the first column as names (as text variables) and the second columns as values. You can use paired variables one with text one with value. Then stack() and augment() them. You can use delimeters in this method by using text variables in columns of the matrix. "," is a text variable that can be in a matrix cell. In this way in Excel you know what is what so no mistakes and transfer can be audited.

p_text:="Pressure" p:=5.5

t_text:="Temperature" t:=27

c0:=stack(p_text, t_text)

c1:=stack(p,t)

out:=augment(c0, c1)

Note you could put in any delimeter - here is a comma.

c0:=stack(p_text, t_text)

c1:=stack(",", ",")

c2:=stack(p,t)

out:=augment(c0, c1, c2)

Now get the one variable "out" to Excel.

Lots of variables can be grouped first then you stack() and augment() the groups so it is a bit more organised, structured, and readable by others.

gc0:=stack(t_group1, t_group2, t_group3)

gc1:=stack(v_group1, v_group2, v_group3)

gout:=augment(gc0, gc1)

Now get the one variable "gout" to Excel.

For example for finite elements have groups for general problem information, nodes, elements, materials, supports etc. Can use Excel or other softwares like this by creating input files for them.

I make files in MathCad that form input files to other software as well as to Excel.

Programming in MathCad is fantastic for this type of data generation.

Range variables to create vectors and matrices are fantastic for this type of data generation.

In this way Mathcad is like a parametric control over input to other software. Change the parametric inputs at top of MathCad file and resulting output file created ready for processing in other proprietary software like FEM and CFD. 1000's of variables in files you can read with variable, titles, text, delimeters, and even comments.

Sometimes you need blank values to keep a rectangular matrix where some lines of an input file need less values. Simply pad out the end of these lines with text variables in the rectangular matrix with no content by double quotation marks "".

These vacant pads are not read in other software as they are not exported or copied. You need them for MathCad to process a rectangular matrix for stack() and augment(). You can see this in the example and the unrectangular result in Excel file enclosed.

or you can directly assemble a matrix with text and values.

a[0,0:="Pressure"

a[0,1:=5.5

a[1,0:="Temperature"

a[1,1:=27

Now get this vector/matrix "a" into Excel. In the sample file some text variables have double quotation marks. This is required not for Excel but for STRAND7. Look at the sample output file in a text editor to see these variables are in the file with quotation marks while others are just the text.

Method A -

If you only want to do it once then copy and paste the vector/matrix.

i=0..10 ---- set a range

v[i=(i+1)/3 ---- create a vector "v" with the variable values.

type in v= and you get a table,,

right click on table -> select all,

right click on table -> copy selection

now simply paste in Excel presto all the values. Works for vector "v" or matrix "out", "gout" and "a" as above. It can copy the text variables and numeric so you know what is what in Excel.

Method B -

Export the vector/matrix to an Excel File.

Command on menu is Insert | Data | File Output.

You can name the file and can have the one input variable that is the vector/matrix.

Always uses the same file name unless you edit the "File output" component.

This is used in the example enclosed. Please the line is disabled so the file is not written till you correct the name to suit you - then and olny then enable the line. .prn is used as the extension as this tells Excel to import using the text wizard.

Method C -

Export the vector/matrix using WRITEPRN(file).

Excel can read a PRN file. As can lots of other software because it is simple text.

This way the file name is a variable and can change according to calculations using string functions.

You may need to set PRNPRECISION and PRNCOLWIDTH worksheet options - see help file.

Hence parametric control of other software by creating a number of files by changing variables and file names in Mathcad.

>>>>>>>>>>>>>>>>>>>>>>> !!!!!!!!!!!!! Warning Following Is An Obscure Use !!!!!!!!!!

Have random or user defined variable names and what you are seeking is the initial assigned values - this works in MathCad 14. Wierd method but does have a valid worksheeet audit use.

Method D -

Not for the faint hearted this method and not documented. Only gives you intially assigned values and I sometimes use this for worksheet audit purposes when people assign values all over the place throughout a long worksheet and your job is to check their work. This gives all their start value assumptions.

Mathcad uses files. Excel can ?sort of? read them direct.

In the huge resulting output, the variables are actually included with their values initially assigned.

You just need to know where to look in the Excel cells.

For instance in MathCad.

set a := 1.1

set b := 2.2

set c := a+b

set d := 5.5

Save as TestVariables.xmcd

Now open this in Excel (using a *.* filter) you get a warning message - say Yes because you want to open it

You now get a question with radio button multiple choice answer - check button "As an XML Table"

You now get an information dialog saying Excel will create the schema - press OK

use edit find for "preserve"

Hey presto the variable names and initial values for "a" and "b" and "d" are shown and in Excel. Not the calculated value for "c".

Delete the rest of sheet. Have a collection of start values for the sheet as an audit trail. Great way to check many input values and to ensure no silly ones not intended are unfortunately left in an important MathCad worksheet.

Regards.

Hi!, Dear Arne,

Here is small program for you:

Private Sub CommandButton1_Click()

Set objMCApp = CreateObject("Mathcad.Application")

objMCApp.Visible = True

Set objmcwks = objMCApp.Worksheets.Open(Me.Range("b1").Value)

Set objresult = objmcwks.GetValue(Me.Range("c3").Value)

'Set objresult = objmcwks.GetValue("a")

Me.Range("b3").Value = objresult

Set objresult = objmcwks.GetValue(Me.Range("c4").Value)

'Set objresult = objmcwks.GetValue("b")

Me.Range("b4").Value = objresult

Set objresult = objmcwks.GetValue(Me.Range("c5").Value)

'Set objresult = objmcwks.GetValue("c.fs")

Me.Range("b5").Value = objresult

End Sub

Regards,

Hedayat