f



Access to Open Excel Cell

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.


0
1/28/2010 7:44:07 AM
comp.soft-sys.math.mathematica 28821 articles. 0 followers. Follow

1 Replies
9744 Views

Similar Articles

[PageSpeed] 53

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


0
marxer (143)
1/29/2010 12:49:16 PM
Reply:

Similar Artilces:

Mathematica to open an Excel spreadsheet and inject output into designated cells
Mathgroup: (1.) Imagine that you have an Excel spreadsheet workbook in your C:\Temp drive. The following data is contained in a worksheet called "Data" of the workbook. Cell D1 contains the word "Year", and cell E1 contains the label "Region 1". Cells D2 to D39 (i.e.; the year column) contain the years 1972 to 2009. Cells E2 to E39 (i.e.; the Region 1 column) contain data values. The data values are (in Mathematica format): {{1972, 5331.}, {1973, 5932.}, {1974, 5656.}, {1975, 5382.}, {1976, 5357.}, {1977, 5680.}, ...

Re: Mathematica to open an Excel spreadsheet and inject output into designated cells #3
You need to treat Excel as a database not an entire file that needs to be consumed. First have an ODBC or any other kind of (JDBC) Database connector Driver for Excel available on your system. Second Use Mathematica's SQL features http://www.connectionstrings.com/excel Look around for proper connection string, also be concerned with permissions issue on your system. Use the distinct SQL statements special to Excel. Use [SheetNames] and R[1]C[1] for ranges. Use SQL SELECT for retrieval Use SQL Insert for insertion/update. Focus on the data not the charts as if your...

Re: Mathematica to open an Excel spreadsheet and inject output into designated cells #4
Hi, there is no Export routine in Mathematica capable to manipulate single numbers or to add something without touching the rest. However, it is easy to do that what you want combining Import and Export "on foot": First you definitely should load your complete "Data" worksheet: In[1]:= tableau = Import["C:\\Temp\\Spreadsheet.xls", {"Sheets", "Data"}] Out[1]= {{"", "", "", "Year", "Region 1"}, {"", "", "", 1972., 5331.}, {"", "&q...

Re: Mathematica to open an Excel spreadsheet and inject output into designated cells #7
Thank you Ulf-Dietrich for your valuable help! However, what I find incredible is that a user can access data from an Excel workbook (and worksheet) into Mathematica with surgical precision but, a user cannot transfer output from Mathematica to the same Excel workbook (and worksheet) without erasing the original workbook! --- On Fri, 5/6/11, Ulf-Dietrich Braumann <braumann@uni-leipzig.de> wrote: From: Ulf-Dietrich Braumann <braumann@uni-leipzig.de> Subject: Re: Mathematica to open an Excel spreadsheet and injectoutput into designated cells Date: Friday, May 6, 2011, 11:...

Re: Mathematica to open an Excel spreadsheet and inject output into designated cells #2
Hi, > > (1.) Imagine that you have an Excel spreadsheet workbook in your > C:\Temp drive. The following data is contained in a worksheet called > "Data" of the workbook. Cell D1 contains the word "Year", and cell E1 > contains the label "Region 1". Cells D2 to D39 (i.e.; the year > column) contain the years 1972 to 2009. Cells E2 to E39 (i.e.; the > Region 1 column) contain data values. > > The data values are (in Mathematica format): .... > > (2.) Mathematica reads the "Region 1" heading in cell (1,5) of th...

Opening Excel from Access
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub Storage_Click() On Error GoTo Err_Storage_Click Dim ExcelApp As Object Dim ExcelWasNotRunning As Boolean ' Flag for final release Dim XLFilePath As String Dim XLName As String ' Excel file name from Paths Dim MyDb As Database Dim Msg As String ' Find the normal path ' Folder and F...

Open Excel from Access
I have an Access XP Database. On one of the forms I have a button that opens and Excel Spreadsheet as follows: Dim xl As Excel.Application Set xl = New Excel.Application xl.Application.Workbooks.Add xl.Cells(1,1) = "Some Text From the database" etc. I have set a reference to the Excel 10 Object Library. The problem I have is that I have to send this application to different users in our company, and some have Excel 2000, in which case they get an error re the Excel 10 Object Library. Is there a way that the code will work regardless of what version of Excel is on the ta...

Excel Cell Math
Is there a way to do the following: =1400/B3-1 Where B3-1 will return the value of the cell directly above it. Thanks Bill Stout wrote... >Is there a way to do the following: =1400/B3-1 Where B3-1 will return >the value of the cell directly above it. In a generic location, =1400/INDIRECT("R[-1]C",0) . On the other hand, if you were entering this formula in, say, X99, why not =1400/X98 ? ...

Error occurs when MS Access open Excel document and trigger the assigned macro in Excel
Hello all I create a form with three buttons in MS Access 2000. They are Open Excel Template, Save Draft and Save Final. When I click the Open Excel Template button, the Excel template will be opened. Inside the Excel template, I have assigned a draft watermark to the Print icon. When the Print icon is clicked, the draft watermark and print dialog box is shown. After the user print/edit data in the Excel template, s/he has to click the Save Draft/Save Final button for saving the excel document. When I open the saved excel document from the MS Access and then click the Print icon, an error oc...

Access to Excel to Access
I have a form with two text boxes, text1 and text2. What I need is a button that exports each text box to a specific cell in an excel file, the excel file needs to be refreshed/updated so a calculation will run and the cell containing the calculation result is passed back to a third text box on the form in access. Ideas? fonzie wrote: > I have a form with two text boxes, text1 and text2. What I need is a > button that exports each text box to a specific cell in an excel file, > the excel file needs to be refreshed/updated so a calculation will run > and the cell containing th...

Excel Crashing when opened from Access
I am using the following code (Office 2000) Private Sub Storage_Click() On Error GoTo Err_Storage_Click Dim ExcelApp As Object Dim ExcelWasNotRunning As Boolean ' Flag for final release Dim MyDb As Database On Error Resume Next Set ExcelApp = CreateObject("Excel.Application") ExcelApp.Visible = True If Err.Number <> 0 Then ExcelWasNotRunning = True Err.Clear ' Clear Err object in case error occurred. ' Check for Microsoft Excel. If Microsoft Excel is running, ' enter it into the Running Object table. Det...

Opening Excel from Access 2000
I have a form with a TextBox that holds the path to an Excel File and a command button that opens the excel file. The Excel file basically contains about 5 maps, one on each worksheet scatter charts on the maps showing locations. I had to use Excel as I wanted each point of the scatter chart labeled and used Bob Bovey's Chart Labler addition to Excel. The data for the scatter charts come from the database ( x & y positions and the appropriate labels If the database is closed the Excel file opens perfectly every time. With the DB open again 100% success. However, if I open the fi...

Open an Excel file from Access
I want to export a table to Excel then open it ....... DoCmd.TransferSpreadsheet acExport, 3, "Table", "ExcelTable", True ..... exports it ... I would like to open it, and possibly even close Access! Ideas? On Tue, 24 Oct 2006 17:07:43 +0100, JohnM wrote: > I want to export a table to Excel then open it ....... > > DoCmd.TransferSpreadsheet acExport, 3, "Table", "ExcelTable", True > > .... exports it ... I would like to open it, and possibly even close Access! > > Ideas? DoCmd.TransferSpreadsheet acExport, 3, "Tab...

[News] Promoting Open Access Versus Inhibiting Open Access
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Can You Make Money from Free Stuff? ,----[ Quote ] | It's good to see companies like Getty Images | trying to include material that's licensed | under Creative Commons licences, but it will | be interesting to see how this all works in | practice. It does, in any case, emphasise | that making money from free stuff, while | perfectly possible, requires careful thought | about the licensing. But then you knew that | anyway. `---- http://www.computerworlduk.com/community/blogs/index.cfm?blogid=14&entryid=3027 BCS EGM: It's...

Web resources about - Access to Open Excel Cell - comp.soft-sys.math.mathematica

Random-access memory - Wikipedia, the free encyclopedia
Random access memory ( RAM ) is a form of computer data storage . A random access device allows stored data to be accessed in any order in very ...

New Brisbane water taxis demand CBD access
Brisbane's first water taxi in decades is about to start operation along the city's eponymous river, but the CBD will remain off limits for the ...

Many Australians struggling to access GPs: Productivity Commission report
Struggling to get into a doctor when you need one? New data shows you are not alone.

New Zealand parents left 'broken' after Australian-born child denied NDIS access
Severely disabled children born in Australia are being denied access to the NDIS because their parents are New Zealand citizens.

16 million Americans have no wired broadband access, not even at 4Mbps
... even if the FCC revived that slower definition of broadband, the commission's annual reports would still find that many Americans lack access, ...

SimPrints founders recognized by Forbes for fingerprint solution for healthcare access
SimPrints co-founders Toby Norman, Tristram Norman and Daniel Storisteanu have been recognized in the 2016 Forbes 30 under 30 social entrepreneurs ...

Magnises, a private club for elite millennials, is now offering its members access to swanky nightclubs ...
... wants to help you build the perfect network. With the use of a personalized black card, the private club gives young professionals access to ...

4 Reasons Tribal Lands Lack Better Access To The Internet
According to the latest data from the FCC , more than two-thirds of people living in rural tribal lands currently lack access to decent Internet, ...

TSA Will Finally Be Given Access To Counterterrorism Database To Screen Airport Workers
TSA Will Finally Be Given Access To Counterterrorism Database To Screen Airport Workers

Morning Digest: Wealthy Democrat channels Trump: 'I sign my checks to buy access'
... asked. While he said he disagrees with the GOP candidates he sent money to "on everything social and economic," "I sign my checks to buy access." ...

Resources last updated: 2/7/2016 2:52:53 AM