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

Programming a loop to seek specific cells from an excel file

SOLVED
Level 11

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

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

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

3 REPLIES 3

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

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

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

Highlighted

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

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

However, I can't share the Excel sheet.

 

Raiko