Prev: Can Mathematica interpolate non-uniform scatter data?
Next: Ha! a document that FAILS because it is in notebook form!
From: Stewart Bodzin on 28 Jan 2010 02:44
I know how to read Excel files into Mathematica. I am trying find a way to
read data from a specific cell in an "open" excel spreadsheet. The basic
problem is to make a change to a cell in excel, and then have a trigger in
Mathematica kick off code to read that cell into Mathematica. I'm not too
concerned on how to trigger the action, and really only need help on how to
code the hook into an open excel spreadsheet to fetch the data from the
excel cell.



Does anyone know of a code example of how to do this.



Anyone's help on this is greatly appreciated.


From: Ariel Sepulveda on 29 Jan 2010 07:44
The Mathematica application in this link
http://www.prontoanalytics.com/products/spreadlike/overview.htm is designed
to generate a trigger automatically anytime the contents of the spreadsheet
is modified. If needed, a trigger can be generated anytime a particular
cell is changed. Other user requirements can be implemented to make it
suitable for other functionalities. My contact info is provided below.
Other data manipulation and analysis applications are available in
http://www.prontoanalytics.com/products.htm.

This an excerpt of the information in the link above:
Sharing is one of the most useful features of SpreadsheetLike. The idea is
that at any time data inside SpreadsheetLike can be used externally for any
user-defined application. The example below shows a dataset where columns A,
B, E, and G are hidden and data is being sorted by column I. By setting
sharing mode to Continuous any change made in the data is continuously being
written to the global variable defined in Variable Name. Thus,
SpreadsheetLike can be used as a data input tool for any user-defined
function.

On Thu, Jan 28, 2010 at 3:44 AM, Stewart Bodzin <Stewart.Bodzin(a)usa.net>wrote:

> I know how to read Excel files into Mathematica. I am trying find a way to
> read data from a specific cell in an "open" excel spreadsheet. The basic
> problem is to make a change to a cell in excel, and then have a trigger in
> Mathematica kick off code to read that cell into Mathematica. I'm not too
> concerned on how to trigger the action, and really only need help on how to
> code the hook into an open excel spreadsheet to fetch the data from the
> excel cell.
>
>
>
> Does anyone know of a code example of how to do this.
>
>
>
> Anyone's help on this is greatly appreciated.
>
>
>


--
Ariel Sepulveda, Ph.D.
__________________________
President, Pronto Analytics Inc.
Tel. 787.354.6947
ariel.sepulveda(a)prontoanalytics.com
http://www.prontoanalytics.com
From: Norbert Marxer on 29 Jan 2010 07:49
On Jan 28, 8:44 am, "Stewart Bodzin" <Stewart.Bod...(a)usa.net> wrote:
> I know how to read Excel files into Mathematica. I am trying find a way to
> read data from a specific cell in an "open" excel spreadsheet. The basic
> problem is to make a change to a cell in excel, and then have a trigger in
> Mathematica kick off code to read that cell into Mathematica. I'm not too
> concerned on how to trigger the action, and really only need help on how to
> code the hook into an open excel spreadsheet to fetch the data from the
> excel cell.
>
> Does anyone know of a code example of how to do this.
>
> Anyone's help on this is greatly appreciated.

Hello

I gabe an answer to a similar question in January 17 2009.

See
http://groups.google.com/group/comp.soft-sys.math.mathematica/browse_thread/thread/281b028237bd26ea/0a1f6bc545ce9389?lnk=gst&q=marxer+excel#0a1f6bc545ce9389

For your convenience I copied my answer here:

(*Begin of quote*)

You can write directly in an existing Excel file using NETLink. For
details see the documentation in the Help Browser at "NETLink/
tutorial/
Overview". I recommend to run the example "ExcelPieChart.nb".

If everything works OK you are ready to attack your problem.

The following commands will load the package, install NET, start
Excel, make it visible and start a dialog Window to open an existing
Excel file:

Needs["NETLink`"]
InstallNET[]
excel = CreateCOMObject["Excel.Application"]
If[ ! NETObjectQ[excel], Return[$Failed]]
excel[Visible] = True
excel[FindFile[]]

This selects the Excel Workbook and Excel Worksheet (here the first
worksheet):

workbook = excel[Workbooks[1]]
worksheet = excel[Workbooks[1][Worksheets[1]]]

This writes a title into the "A1" Excel cell and sets the font:

worksheet[Range["A1"][Value]] = "Primzahlen";
worksheet[Range["A1"][Font[Bold]]] = True;

This specifies a range:

start = "B3"; cols = 2; rows = 10;
srcRange = worksheet(a)Range[start]@Resize[rows, cols]

This defines a table of numbers and writes it into Excel :

values = Table[{i^2, Prime[i]}, {i, rows}];
srcRange(a)Value = values;

You can even create a chart:

chartCastSep =
CastNETObject[workbook[Charts[][Add[]]],
"Microsoft.Office.Interop.Excel.ChartClass"]
chartCastSep(a)ChartWizard[srcRange];

If you prefer a XY scatter plot then:

LoadNETType["Microsoft.Office.Interop.Excel.XlChartType"]
chartCastSep[
ChartWizard[srcRange, XlChartType`xlXYScatter, format = 1,
plotBy = 2, catLab = 1, serLab = 0, hasLegend = True, "Title",
"CategoryTitle", "ValueTitle", "ExtraTitle"]]

With some Excel knowledge you can even read and write the formula in
the Excel cells or read and write Excel Macro code.

I hope this helps and good luck.

(*End of quote*)

Best Regards
Norbert Marxer


 | 
Pages: 1
Prev: Can Mathematica interpolate non-uniform scatter data?
Next: Ha! a document that FAILS because it is in notebook form!