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

Community Tip - Want the oppurtunity to discuss enhancements to PTC products? Join a working group! X

Programming a loop to seek specific cells from an excel file

Raiko
16-Pearl

Programming a loop to seek specific cells from an excel file

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

1 ACCEPTED SOLUTION

Accepted Solutions

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;
}
}
}

View solution in original post

3 REPLIES 3

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.

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;
}
}
}

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

However, I can't share the Excel sheet.

 

Raiko

Top Tags