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