0
$\begingroup$

I have a macro (is that what you call it?) set up in Mathematica and it runs fine. What I want to do now is set up my notebook to look through an Excel document (.xlsx) and pick out a certain set of cells that I named in Excel using the name manager. How do I get Mathematica to import that Named Range?

Also I have noticed that Mathematica will not recognize every excel document and I have checked to make sure that the file is an xlsx file and still Mathematica tells me that the file can't be opened as an xls.

So two problems.

  1. Mathematica doesn't accept every file name I give it and reports the it cannot be imported in XLS format.
  2. How to I import a named cell from Excel without having to use row and column descriptors?

Per request here is what I have tried so far

Import["NajaK5uLData.xls"]

Import::nffil: File not found during Import. >>
$Failed

Import[NotebookDirectory[] <> "NajaK5uLdata.xlsx"]

Import::fmterr: Cannot import data as XLS format. >>
$Failed

Improve this question
$\endgroup$
4
  • $\begingroup$ Normally, Mathematica users don't call anything in Mathematica a macro. $\endgroup$
    – m_goldberg
    Commented Jun 21, 2013 at 13:32
  • $\begingroup$ Please edit your post to show the code you have tried so far. $\endgroup$
    – m_goldberg
    Commented Jun 21, 2013 at 13:33
  • $\begingroup$ Perhaps put your spreadsheet on a server where we can download it and see if one of us can load it into Mathematica. $\endgroup$
    – Michael Stern
    Commented Jun 22, 2013 at 15:16
  • $\begingroup$ It might be helpful if you could provide a link to a description of what named cell ranges are. Most of us are not Excel experts, and personally it's the first time I've heard of this. Since it sounds like it might be a new feature in Excel 2007, you should be aware that not all features of the XLSX format are fully supported so far. $\endgroup$
    – Oleksandr R.
    Commented Jun 22, 2013 at 20:22

2 Answers 2

Reset to default
2
$\begingroup$

I have never had a problem importing any kind of Excel file into modern versions of Mathematica. What version are you using? In version 9, this works:

illustration = 
  Import[NotebookDirectory[] <> "settlements-20130607ß.xlsm", 
    "Data"][[14]];

Note that it works for .xlsm files just as easily as it would for .xlsx or .xls. Also note that I can discard everything but a particular tab (tab 14 in this example), if I know the information I want is there.

In response to your second question, I do not believe there is any way to refer to Excel's named ranges from within Mathematica. Why not have an extra tab ("mathematica export" or whatever) in your Excel workbook, and then refer to your named range there in a known location. Mathematica can then import that tab and your data will be immediately on hand.

Improve this answer
$\endgroup$
2
  • $\begingroup$ Well I am using version 8.0. I did think of just specifying a sheet and then a cell, I just thought it would have been clever if Mathematica could find cell names, plus I'd think it would be simpler since cell names are workbook and not Sheet specific. I tried using the NotebookDirectory but I didn't specify the file and it listed several files that I had. However, the one I was searching for did not appear on the list. Sorry I'm pretty new to this $\endgroup$
    – CouldntThinkOfAName
    Commented Jun 21, 2013 at 15:31
  • $\begingroup$ Thank you for your help but I think I am just got to create a text file and import from there. Sorry I couldn't adequately explain my problem. Once again thank you. $\endgroup$
    – CouldntThinkOfAName
    Commented Jun 24, 2013 at 11:50
0
$\begingroup$

I found a way of importing a range of cells from a particular sheet in an Excel file:

Import["PathOfFile\\ExcelFile.xlsx", {"Sheets", "NameOfSheet", 
   Table[i, {i, 2, 28}], Table[i, {i, 1, 4}]}];

Imports rows 2 to 28, columns 1 (A) to 4 (D) from Sheet "NameOfSheet" in Excel file "ExcelFile.xlsx" in path "PathOfFile", and does it as a matrix (list of lists), in this case of size 27 x 4.

Improve this answer
$\endgroup$
1
  • $\begingroup$ The question asks for help in reading a named range of cells from an Excel spreadsheet. This post does not answer the question. $\endgroup$
    – High Performance Mark
    Commented Apr 14, 2020 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.