Skip to main content
M
MM_109461305-Regular Member
5-Regular Member
May 20, 2024
Question

Output Mathcad Matrix to Excel with VBA

  • May 20, 2024
  • 3 replies
  • 2120 views

Using Prime 9.0

Hello,

I have an Excel program with VBA code. When you click the "Calculate This Tab" button, it configures the input values in the designated Mathcad sheet (relative file path in cell D3) , and calculates the outputs accordingly. The point is that these values can easily be changed from Excel and the outputs are calculated based on the latest values and outputted back to Excel. The real program is working on a much larger scale, but I created a small scale version for simplicity here.

The problem I am having is when trying to output a Matrix back to Excel. I have not been able to get it to work in a seamless way. One thing I have tried is writing the Matrix output to a temporary csv file and then importing the data into excel through a sub function defined in the VBA code. This sort of works, but the problem with this is that the WRITECSV() function in Mathcad does not update when I run the macro from Excel even if I assign it as an Output like the others. I have to manually go into the Mathcad sheet and make sure it re-calculates to output the right data which essentially defeats my goal of using the Excel-macro interface to automate this process. In the VBA code, I am clearing the temp csv file after importing because I'd like to ensure the matrix output is based on the latest values in the excel and is not still showing an output from a previous configuration.

MM_10946130_0-1716178326202.pngMM_10946130_1-1716178342291.png

 

I am open to other suggestions on how I can better get the matrix output to my excel sheet if there is a better way of outputting the "Output" matrix to Excel directly instead of using the intermediary csv file, I just wasn't sure how else to do it. 
I'd appreciate any help with this and let me know if anything needs more clarification. I attached the zip folder with the files.

3 replies

T
21-Topaz II
terryhendicott21-Topaz II
21-Topaz II
May 20, 2024

Hi 

Mathcad Prime COM API can be run from an Excel VBA program?  This means the Prime output data to Excel is possible and is ensured to be fresh when running the Excel and can be inserted into the Excel sheet.  You do not need to use csv file but transfer direct from Prime to Excel.

 

Will take some time to work this up for you as I am really busy this week.

 

Cheers

Terry

 

    M
    MM_109461305-Regular MemberAuthor
    5-Regular Member
    May 21, 2024

    Hi Terry,
    If you're able to work on this by sometime tomorrow, that'd be great. The main part is being able to output the matrix back to the excel sheet on each sheet run, but I understand if you're busy with other things.

    Thanks

    V
    vnamboodheriCommunity Manager
    Community Moderator
    May 27, 2024

    Hello @MM_10946130,

     

    It looks like you have a response from a community member. If it helped you solve your question please mark the reply as the Accepted Solution. If you found a solution on your own, please document it and mark it as accepted solution. This will surely help other users who may have a similar question.

    Of course, if you have more to share on your issue, please let the Community know so other community members can continue to help you.

    Thanks,
    Community Moderation Team.

    M
    MM_109461305-Regular MemberAuthor
    5-Regular Member
    June 18, 2024

    I am still looking for a solution to this if anyone is able to help.

    T
    21-Topaz II
    terryhendicott21-Topaz II
    21-Topaz II
    June 23, 2024

    Hi,

    Sorry it has taken so long I needed to find a block of a few hours to understand your coding, been very busy.

     

    Added    using Tools | References in VBA Developer window the PTC Automation objects.  You can now see them in the object browser.

    This means a couple of changes to your syntax but sure you can follow this by comparing the codes

    Only altered the VBA coding in Sub Calculate_Current_tab() macro.

     

    It now handles a matrix composing of only numbers and transfers it to the Excel worksheet from Mathcad.

     

    Here is the bad news.  I have never successfully transfered a matrix of strings and numbers.  Will try again but it may take a while and may not be possible.

     

    Cheers

    Terry

    Terms & ConditionsCookie settingsAccessibility statement