topic Re: Programming a loop to seek specific cells from an excel file in Mathcad

Programming a loop to seek specific cells from an excel file

Raiko — Mon, 07 Jan 2019 12:22:38 GMT

Hello folks,

I have an Excel sheet in which caloric properties for real gas are calculated. One of the values is only delivered when a VB code is executed in Excel. I strongly suspect that these are in a data map of sorts which is what I'm after. Since I can't access this table (the guy who created this sheet has left long since) I'd like to "push" the appropiate button in Excel from a Mathcad loop repeatedly and read out the data in a specific cell.

Does anybody know whether this is feasible?

Raiko

Re: Programming a loop to seek specific cells from an excel file

terryhendicott — Tue, 08 Jan 2019 08:27:19 GMT

Hi Raiko

It is possible to automate both Mathcad and Excel using the API of both. This includes running Excel macros.

From the web

"Is there any api for executing a macro code of an excel? I don't want to use office interop and I don't want to recode using aspose, I have lot of existing macros I just want to know if there's an api that can do that?

To run a macro, you must create an environment in which the macro can run. To create such an environment you need to automate Excel. You can do this in an Excel add-in or in a standlone application in .NET or in Delphi, VB6, VC++ or other language. After you instantiate the Excel.Application object, you need to call the method it provides - RunMacro."

The other option is to run the macro by hand and use Mathcad's Readexcel component to read the values into Mathcad.

Re: Programming a loop to seek specific cells from an excel file

terryhendicott — Wed, 09 Jan 2019 04:15:25 GMT

Hi,

Here is a sample of automating Excel. It enters some values in cells, runs a macro called "test", then fetches a cell value.

For a sample of automating Mathcad Prime look at "Re: Mathcad Prime Interface / API" in section of Top Kudoed Posts.

Do you want someone to do the automation for you, if you can share the sheets?

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

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

private static Excel.Application MyApp = null;
private static Excel.Workbook MyBook = null;
private static Excel.Worksheet MySheet = null;
private int lastRow;

private void button1_Click(object sender, EventArgs e)
{
MyApp = new Excel.Application();
MyApp.Visible = true;
MyBook = MyApp.Workbooks.Open(@"D:\\Prime\\Questions\\Book1.xlsm");
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;

lastRow += 1;

MySheet.Cells[lastRow, 1] = "Six";
MySheet.Cells[lastRow, 2] = 14;
MySheet.Cells[lastRow, 3] = "Test";
MySheet.Cells[lastRow, 4] = 30.9;

MyApp.Run("Test");

double val = MySheet.Cells[1, 4].Value;

MyBook.Save();
MyBook.Close();
MyApp.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(MySheet);
MySheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(MyBook);
MyBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(MyApp);
MyApp = null;
}
}
}

Re: Programming a loop to seek specific cells from an excel file

Raiko — Wed, 09 Jan 2019 08:10:54 GMT

Thank you Terry for the code. Will try it in Mathcad 15.

However, I can't share the Excel sheet.

Raiko