Mathcad and Excel VBA

[Azad_Chowdhery]

Is there any way to send excel data into Matchcad using VBA. I have many claculation work on mathcad but I want excel to be use as an input body which send the specific cell data to a variable in mathcad and the mathcad run the calculation. after that vba send the message to print the mathcad worksheet Example is here to understand

Step 1 In excel sheet, we have let say three values in three cells variable with values such as a is 350, b is 505 and c is 900 and so on (actually I have more than 100 values)

Step 2 Excel vba open mathcad using COM or API (I do not know the code yet and how to open mathcad from excel vba)

Step 3 Excel sent a, b, and c value in mathcad.  where many operation are performed such addition, square and many more using these a,b and.

Step 4 Excel send signal to print mathcad and then close the mathcad worksheet.

Note: Please note the actual work is much more complicated it has atlead 60 pages of calculation in mathcad and more than 100 variables need to be sent from excel to mathcad using VBA. Each time I performed the calcualtion, these 100 values are different every time. Right now I put these values manually that takes time and may have errors

Is there any solution available like I mentioned in four steps?

Thanks in adance.

I am new in mathcad.

[terryhendicott · Accepted solution]

Hi,

Step 1 In excel sheet, we have let say three values in three cells variable with values such as a is 350, b is 505 and c is 900 and so on (actually I have more than 100 values)

Put the 100 values in 100 contiguous Excel cells so they can be referred to as one matrix of values to transfer to Prime.

Step 2 Excel vba open mathcad using COM or API (I do not know the code yet and how to open mathcad from excel vba)

Step 3 Excel sent a, b, and c value in mathcad.  where many operation are performed such addition, square and many more using these a,b and.

The enclosed spreadsheet has 100 cells with values and the VBA macros and forms shows how to transfer the values either way.

A button on the Excel worksheet shows a form.  The form has a Go button.  Press Go and Excel opens Mathcad transfers 100 values to Prime. Prime multiplies them all by three.  Excel reads the values back and shows them in the form.  Be patient as there is a pause time built in.

Step 4 Excel send signal to print mathcad and then close the mathcad worksheet.

Use the VBA commands below to save and close the file.  Don't know if VBA can print the Prime file?

WS.Save
MCApp.CloseAll (SaveOption_spSaveChanges)

[Azad_Chowdhery]

Hi Terry,

Thanks for the solution. It really helped me.

I am stuck in matrix. I need to assign these values as real number instead of matrix

For example 

In excel we have:

A1 cell has 25

A2 cell has 99

A3 cell has 2568

So in Mathcad 

a1=25

a2=99

a3=2568

and then few calcuation is performed

Note: I have different units for these values for example kg, N, Nm etc

Unfortunately I am new with mathcad and no idea how can convert matrix value to the real numbers.

The example you have provided it running flawlessly. I really appreciate that you find time and helped me 🙂

Thanking you in advance 

[LucMeekes]

Beware that the default origin for arrays (vectors and matrices) in Mathcad is 0, not 1, so your first array element may be a0 in Mathcad.But you can set the ORIGIN to 1, if you want to refer to the first array elemenet with index 1.

You say you need 'real' numbers, I guess you mean that you need scalars (as opposed to arrays), to work with you calculations in Mathcad. Well, if your array in Mathcad is called a, and it has three elements, the first element (by default) is a0, and you get it by typing (in mathcad): a[0

and this a[0 is a scalar. If you want to refer to it otherwise you can assign it to a variable with an appropriate name, e.g.:

Mass:a[0

(This will show as:    )

Excel doesn't know about/work with units. Hence there is no way to export united values from Mathcad to Excel, or import values with units. There are (at least) two methods to work around this:

1. You work with implied units: e.g. a1 is (always assumed to be) in kg, a2 in N and a3 in N*m. Then when you assign a1 to the variable Mass, you apply the correct unit. E.g.:

Mass:a[1*kg, which shows as .

2. You add in Excell a second column (B) next to the column with values. And you put in that column the units for each of the values as string values ("kg", "N" and "N*m"). In Mathcad you now must fetch the two columns from excel simultaneously, that will result in a 2 column matrix in mathcad. Now you can examine the unit strings for each of the values and apply the respective units. Note that there is no built in feature in Mathcad to do so, so you have to write your own mathcad program to apply these units, making sure that you account for every possible unit string that you want to use in Excel.

I suggest you start with option 1.

Success!
Luc

[Azad_Chowdhery]

Is there any way in mathcad to convert the matrix or vector into scalar? I do not need data back to excel. If it can tranfer simple values from excel to mathcad and assign to individual variable that work perfect for me (unit is not a problme for me). The example file you have given, it is assigning the entire hundre value from excel to one vector in mathcad instead I want these hundree values are assigned to hundred of variable without the square bracket [,] in mathcad. 

In some of excel cell I have drwaing and item no as 1AB5320-23A and LTH-MNEAS 

I want this also to be sent from excel to mathcad and that can be assigned in mathcad  as Drwaing number=1AB5320-23A  and item no=LTH-MNEAS

[terryhendicott]

Hi,

There is two ways to handle this.  I prefer the second as it keeps track of variable names.

[Azad_Chowdhery]

These option with direct insertion of excel read command has one flaw. When some value from excel get updated it does not update accordingly. I am trying to make it simple. I have Creo where my data is available, 1st step to fetch data from Creo and save in excel sheets then these data are tranfered to mathcad in simplified way rather than typing those in mathcad. like vlookup in mathcad an so on... I have several variation of product and want to simplify it to reduce my workload

[terryhendicott]

Hi,

Simplest is to use array indexing.

[ChrisKaswer]

If your goal/need is to share numbers between Creo and Mathcad, why bother using Excel as a middle step? Just use the direct connection tool that allows a 2-way data sharing between Creo and Mathcad. If you need to store these values in Excel for other use, simply write them to Excel from Mathcad. This would simplify your process significantly.

[terryhendicott]

Hi,

Using VBA is not the only way to transfer values from excel to Prime.

A function called READEXCEL is also available and is simpler if all you want to do is transfer values from Excel to Prime.

Cheers

Terry

[CP_9333803]

I am maintaining an Excel template that can iterate through a Mathcad worksheet using VBA. It's available and documented here: www.radianrad.com/met.