Solved: API: Prime 7 - Excel. Matrix passing issues.

DM_10127316 · ‎Jan 12, 2022

Hi,

I managed to pass variables from Excel to Prime 7 and then back to Excel using Visual Basic. The code works as intended. Here is the code

Private Sub CommandButton2_Click()

Dim a, b, c As Integer
Dim RowNum As Integer
Dim Path As String

Dim MC As Ptc_MathcadPrime_Automation.Application
Dim WS As Ptc_MathcadPrime_Automation.Worksheet

Path = CurDir & "\" & ActiveSheet.Range("J4").Value

a = ActiveSheet.Range("C2").Value
b = ActiveSheet.Range("C3").Value
c = ActiveSheet.Range("C4").Value

Set MC = CreateObject("MathcadPrime.Application")
MC.Visible = True
Set WS = MC.Open(Path)

WS.SetRealValue "a", a, ActiveSheet.Range("D2").Value
WS.SetRealValue "b", b, ActiveSheet.Range("D3").Value
WS.SetRealValue "c", c, ActiveSheet.Range("D4").Value

WS.Synchronize

Set RawOut = WS.OutputGetRealValue("out")
ActiveSheet.Range("C7").Value = RawOut.RealResult
ActiveSheet.Range("D7").Value = RawOut.Units

End Sub

The issue arises when I try to pass multiple variables (100+) using an array or matrix. Meaning, taking an array or Matrix of size (n,1) from excel, add it to a variable in Prime (lets say, M), then using the elements of M to populate the Prime file and retrieving the output back to Excel.

Somehow, when I jump from single values to matrices my code crashes.

I found this thread

https://community.ptc.com/t5/PTC-Mathcad/communication-between-VBA-and-Prime7-SetMatrixValue/m-p/769060

where the OP seems to have the same issue. I tried all the solutions there but I keep getting errors. I can't even define a matrix using

Set matrix = WS.CreateMatrix(3, 3)

As one anon did.

Anyone has any idea how to do this?

Thank you!

terryhendicott · ‎Jan 13, 2022

Hi,

Here is the visual basic version:

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

' Close

’Try
'WS.Close(Ptc.MathcadPrime.Automation.SaveOption.spDiscardChanges)
'Catch
'MessageBox.Show("Couldn't close worksheet, make sure Mathcad Prime is running")
'End Try

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

View solution in original post

terryhendicott · ‎Jan 12, 2022

Hi,

The following code "Inputs" into Prime worksheet a 3 x 3 matrix.

Prime multiplies the matrix by three.

Prime "Outputs" the now larger matrix to the calling program and displays it in a rich text box.

The program works by hitting a go button.

It is programmed in Visual Studio 2019 in C#.

The actual code is as below.

I have enclosed a zip file with the mathcad sheet and the visual studio solution.

The file worksheet name is hard coded

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Ptc.MathcadPrime.Automation;

namespace PrimeMatrix
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
var appCreate = new ApplicationCreatorClass();
ApplicationCreator app;
app = (ApplicationCreator)appCreate;
IMathcadPrimeWorksheet3 ws;
ws = (IMathcadPrimeWorksheet3)app.Open(@"F:\\Prime\\VB\\Test.mcdx");

app.Visible = true;

IMathcadPrimeWorksheet wsTest = app.ActiveWorksheet;
ws = (IMathcadPrimeWorksheet3)wsTest;

IMathcadPrimeInputs input = ws.Inputs;
int countinput = input.Count;
String alias = input.GetAliasByIndex(0);

IMathcadPrimeOutputs outputs = ws.Outputs;
int b = outputs.Count;
String alias2 = outputs.GetAliasByIndex(0);
ws.SetRealValue(alias, 12.5, "m");

textBox1.Text = alias + " - " + alias2;

int i = 5;
IMathcadPrimeMatrix matrixValue = (IMathcadPrimeMatrix)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, "");

IMathcadPrimeOutputMatrixResultAs hereResult = ws.OutputGetMatrixValueAs("here", "");
double wshereResultRowsCount = hereResult.MatrixResult.Rows;
double wshereResultColsCount = hereResult.MatrixResult.Columns;

double dblhere;

for (int ic = 0; ic < wshereResultRowsCount; ic++)
{

for (int jc = 0; jc < wshereResultColsCount; jc++)
{
hereResult.MatrixResult.GetMatrixElement(ic, jc, out dblhere);
richTextBox1.AppendText(Convert.ToString(dblhere));
richTextBox1.AppendText(", ");

}
richTextBox1.AppendText("\n");
}
}
}
}

terryhendicott · ‎Jan 13, 2022

Hi,