cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - New to the community? Learn how to post a question and get help from PTC and industry experts! X

extracting matrix values from mathcad to Excel

JF_9255051
4-Participant

extracting matrix values from mathcad to Excel

Hello, 

 

i am not able to extract my calculated matrix from mathcad prime 7 to excel. I was able to hand over parameters from excel to mathcad and now i want to retrieve the solution. 

 

Dim matrix As IMathcadPrimeOutputMatrixResult
Set matrix = WS.OutputGetMatrixValue("test")

 

From there I would extract all the elements one by one.

I also changed the outputalias from "out" to "test" but I only receive the ErrorCode 7 "Matrix result is invalide".

Can somebody give me a general example how I can retrieve my results into Excel by the api.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi,

No example exists in Help for Visual Basic For Applications VBA using the Prime API to input and output matrices.

 

Here it is.  This is a long post.

 

Enclosed are two files an Excel 2019 spreadsheet and a Prime 7 worksheet.

Capture.JPG

Prime 7 worksheet has two inputs and two outputs declared.

Capture7.JPG

Excel worksheet has nothing but a command button that runs Macro1 when clicked

Capture2.JPG

Macro1 only shows a VBA form.

Capture4.jpg

The VBA Form has a Go button.  Press the Go button and the excel subroutine associated with the click of the button does a few things

Capture5.JPG

a) It Runs Prime and opens the worksheet.  The location is simply coded in VBA so Path and Filename will need to be adjusted for where you save the two files.

b) It Inputs into Prime a value with units and a matrix.   The values are coded in VBA. 

    The example does not show how values can be extracted from Excel to VBA as this is covered by VBA Help.

    The example does not cover how values in VBA are sent to Excel as this is covered in VBA Help.

c) It Outputs from Prime the names of the first Inputs and Outputs the output matrix.

   

d) It shows the Output matrix from Prime into a VBA text box.

Capture6.JPG

 

The VBA IDE needs to be set up so the PTC Automation is available to the VBA coding.

Once this is done you get code completion highlights in the VBA IDE

Use Tools | References to check mark against the PTC Automation option.

Capture8.JPG

This brings up a dialog box where you can search for and check the PTC Automation Information.

Capture9.JPG

Once this is done you can use the object browser of the VBA IDE to inspect the objects, properties, and methods of the PTC Automation

Capture10.JPG

The PTC Automation is available in the drop down list of the object inspector. 

From here you can inspect all the properties, methods of PTC Automation

Capture11.JPG

 

VBA Coding is wholly enclosed in the subroutine "Private Sub CommandButton1_Click()"

I will leave it for others to explore.

Capture12.JPG

The two files are included in enclosed zip file.

 

Cheers

Terry

View solution in original post

7 REPLIES 7
JF_9255051
4-Participant
(To:LucMeekes)

Thank you for your help.

 

I am able to communicate one solution back, but I want to send a matrix back.

Hi,

Attach you Prime worksheet and your coding so far.

Hi

Using visual basic this file shows how to input a matrix into Prime worksheet and how to output a matrix from Prime worksheet.  Alias is used.

Capture.JPG

Capture2.JPG

Capture3.JPG

 

The code that achieves this is

Snip>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Imports Ptc.MathcadPrime.Automation

Public Class Form1
Private MCApp As Ptc.MathcadPrime.Automation.ApplicationCreator
Private WS As Ptc.MathcadPrime.Automation.IMathcadPrimeWorksheet3

 

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

 

Try
MCApp = New Ptc.MathcadPrime.Automation.ApplicationCreator
MCApp.Visible = True
Catch
MessageBox.Show("Issues starting Mathcad Prime")
End Try

Dim Directory As String
Directory = System.AppDomain.CurrentDomain.BaseDirectory()
OpenFileDialog1.InitialDirectory = Directory
OpenFileDialog1.ShowDialog()
Try
WS = MCApp.Open(OpenFileDialog1.FileName)
Catch
MessageBox.Show("Couldn't open file, make sure you selected a valid file")
End Try

 

Dim pI As IMathcadPrimeInputs
pI = WS.Inputs
Dim c As Integer
c = pI.Count
Dim alias1 As String
alias1 = pI.GetAliasByIndex(0)

Dim pO As IMathcadPrimeOutputs
pO = WS.Outputs
Dim b As Integer
b = pO.Count
Dim alias2 As String
alias2 = pO.GetAliasByIndex(0)
WS.SetRealValue(alias1, 11.5, "km")


TextBox1.Text = alias1 + " - " + alias2

Dim i As Integer
Dim matrixValue As IMathcadPrimeMatrix
matrixValue = WS.CreateMatrix(3, 3)

i = matrixValue.SetMatrixElement(0, 0, 2.5)
i = matrixValue.SetMatrixElement(0, 1, 4.0)
i = matrixValue.SetMatrixElement(0, 2, 3.1)
i = matrixValue.SetMatrixElement(1, 0, 1.5)
i = matrixValue.SetMatrixElement(1, 1, 3.6)
i = matrixValue.SetMatrixElement(1, 2, 5.0)
i = matrixValue.SetMatrixElement(2, 0, 7.2)
i = matrixValue.SetMatrixElement(2, 1, 4.8)
i = matrixValue.SetMatrixElement(2, 2, 6.8)

WS.SetMatrixValue("B", matrixValue, "")

Dim hereResult As IMathcadPrimeOutputMatrixResultAs
Dim rowCount, colCount As Integer

hereResult = WS.OutputGetMatrixValueAs("here", "")
rowCount = hereResult.MatrixResult.Rows
colCount = hereResult.MatrixResult.Columns

Dim dblhere As Double

For i = 0 To rowCount - 1
For j = 0 To colCount - 1
hereResult.MatrixResult.GetMatrixElement(i, j, dblhere)
RichTextBox1.AppendText(dblhere.ToString)
RichTextBox1.AppendText(", ")
Next j
RichTextBox1.AppendText(Microsoft.VisualBasic.ControlChars.NewLine)
Next i
End Sub

 

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Try
MCApp.Quit(Ptc.MathcadPrime.Automation.SaveOption.spDiscardChanges)
Catch
MessageBox.Show("Couldn't close Mathcad Prime, make sure Mathcad Prime is running and all changed worksheets are closed")
End Try
End Sub
End Class

Snip>>>>>>>>>>>>>>>>>>>>>

 

 

Why not use READEXCEL? It seems like you might be reinventing the wheel. You can set the inputs of READEXCEL as variables, and your variables are concatenated strings. As long as you can pass strings to variables and variables to READEXCEL, then you can extract whatever you want from it. 

 

For example:

KevinFinity_0-1643391915705.png

 

Hi,

No example exists in Help for Visual Basic For Applications VBA using the Prime API to input and output matrices.

 

Here it is.  This is a long post.

 

Enclosed are two files an Excel 2019 spreadsheet and a Prime 7 worksheet.

Capture.JPG

Prime 7 worksheet has two inputs and two outputs declared.

Capture7.JPG

Excel worksheet has nothing but a command button that runs Macro1 when clicked

Capture2.JPG

Macro1 only shows a VBA form.

Capture4.jpg

The VBA Form has a Go button.  Press the Go button and the excel subroutine associated with the click of the button does a few things

Capture5.JPG

a) It Runs Prime and opens the worksheet.  The location is simply coded in VBA so Path and Filename will need to be adjusted for where you save the two files.

b) It Inputs into Prime a value with units and a matrix.   The values are coded in VBA. 

    The example does not show how values can be extracted from Excel to VBA as this is covered by VBA Help.

    The example does not cover how values in VBA are sent to Excel as this is covered in VBA Help.

c) It Outputs from Prime the names of the first Inputs and Outputs the output matrix.

   

d) It shows the Output matrix from Prime into a VBA text box.

Capture6.JPG

 

The VBA IDE needs to be set up so the PTC Automation is available to the VBA coding.

Once this is done you get code completion highlights in the VBA IDE

Use Tools | References to check mark against the PTC Automation option.

Capture8.JPG

This brings up a dialog box where you can search for and check the PTC Automation Information.

Capture9.JPG

Once this is done you can use the object browser of the VBA IDE to inspect the objects, properties, and methods of the PTC Automation

Capture10.JPG

The PTC Automation is available in the drop down list of the object inspector. 

From here you can inspect all the properties, methods of PTC Automation

Capture11.JPG

 

VBA Coding is wholly enclosed in the subroutine "Private Sub CommandButton1_Click()"

I will leave it for others to explore.

Capture12.JPG

The two files are included in enclosed zip file.

 

Cheers

Terry

Thank you very much for your support, 

I finally got it to work with the following script that fills the array q in VBA with the results from MathCad:

 

Dim oe As Ptc_MathcadPrime_Automation.OutputMatrixResult
Dim rowCount, colCount As Integer
Dim dblhere As Double
Dim q As Variant
Dim d As Long

Set oe = WS.OutputGetMatrixValue("test")

rowCount = oe.MatrixResult.Rows
colCount = oe.MatrixResult.Columns

ReDim q(rowCount - 1, colCount - 1)

For i = 0 To rowCount - 1
For j = 0 To colCount - 1
d = oe.MatrixResult.GetMatrixElement(i, j, dblhere)
q(i, j) = dblhere
Next j
Next i

Top Tags