MathGroup Archive 2010

[Date Index] [Thread Index] [Author Index]

Search the Archive

Re: Access to Open Excel Cell

  • To: mathgroup at smc.vnet.net
  • Subject: [mg106963] Re: Access to Open Excel Cell
  • From: Norbert Marxer <marxer at mec.li>
  • Date: Fri, 29 Jan 2010 07:49:23 -0500 (EST)
  • References: <hjrf87$n0e$1@smc.vnet.net>

On Jan 28, 8:44 am, "Stewart Bodzin" <Stewart.Bod... at 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@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@Value = values;

You can even create a chart:

chartCastSep =
 CastNETObject[workbook[Charts[][Add[]]],
  "Microsoft.Office.Interop.Excel.ChartClass"]
chartCastSep@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



  • Prev by Date: Re: Journals dying?, apparently rather slowly (was ,
  • Next by Date: Re: Re: Journals dying?, apparently rather slowly (was ,
  • Previous by thread: Re: Access to Open Excel Cell
  • Next by thread: Re: Re: Re: More /.{I->-1} craziness. Schools