readfromexcel

Returns values in multiple ranges of existing Excel file.

Description

VARARGOUT = READFROMEXCEL(FILELOC,VARARGIN)
 
Uses ActiveX commands to read range(s) from an existing Excel spreadsheet.
 
FILELOC: Enter a string representing the (absolute or relative) location of an Excel file. (Extension may be omitted, and will be assumed to be .xls.)
   Examples: 'c:\brett\my archives\test1.xls'
             'test1.xls'
             'myarchive'
 
SHEETNAME: (Optional): Any occurrence in the variable argument list of the strings 'sheetname' or 'sheet' prompts the function to change active sheets to the value in the following variable. That specifier must be a string argument matching exactly the name of an existing sheet in the opened file. If this argument is omitted, the function defaults to reading from the currently active sheet.
 
RANGE SPECIFIER(S): Enter the range(s) to read. The values stored in these ranges will be returned in consecutive output arguments.
   Example: 'B1:B5'
            'B1:B1' (or simply 'B1')
            'B1:P4'
            'B:B' or 'B'
                 (Entire second column)
            '2:2' or '2'
                 (Entire second row)
            'ALL' (Entire sheet)
 
       (Additional ranges: Comma separated ranges in the same form as above; contents of archive will be returned in output arguments 2...n)
 
OUTPUT: If specified range is 1 cell, variable returned is of the same class as cell contents. If the range spans more than 1 cell, the variables will be cell arrays.
 
EXAMPLES:
a = readfromexcel('c:\brett\my archives\test1.xls','C1:C5');
    reads from the currently active sheet

a = readfromexcel('c:\brett\my archives\test1.xls', 'ALL');
    reads the entire used portion of the active sheet

a = readfromexcel('c:\brett\my archives\test1.xls', 'C');
    reads column C of the used portion of the active sheet

[a,b] = readfromexcel('c:\brett\my archives\test1.xls','sheet','sheet2','C1:C5','C1:P3');
    reads from sheet2

[a,b,c] = readfromexcel('myarchive','C3:D5','sheet','mysheet','E4','sheet','sheet2','B3');
    reads a from currently active sheet, switches to sheet 'mysheet' to read b, then to sheet 'sheet2' to read c.
 
Written by Brett Shoelson, Ph.D.
[e-mail]
Update History:
1/04. Version 1.
2/2/04. Now allows multiple specifications of sheet name at the suggestion of R. Venkat), and support of relative paths (thanks to Urs Schwarz). Also, inclusion of the extension '.xls' is now superfluous.
7/29/04. Implements try/catch structure for reading of ranges to avoid errors that leave open activex connections. (Response to Chris Paterson's CSSM query). Also, now accomodates reading of entire sheet, or of entire row/column. (Response to email queries by Kinan Rai and CSSM query by Xiong.)

SEE ALSO: write2excel

• Acknowledgements: This submission has inspired the following:; Cell 'Find And Replace'
• MATLAB release: MATLAB 6.5 (R13)
• Other requirements: Requires Windows environment and Microsoft Excel. Tested under R12.1, R13, and R14. Note that empty cells are returned as NaNs in R14.

[M Dorey]

Very useful.

[Jordan Hall]

Very handy - works great, but runs a little slowly.

[Giuseppe Ridinò]

Sounds good, but I faced problems using it because the online help is confusing and doesn't help too much. BTW.... good work!

[Onur Uncu]

Very very useful! Simple and handy! Thanks Brett!

[Chris Paterson]

Nice little tool that. Great work Brett.

[Charles Caskey]

This app is totally cool!

[V P]

Nice job, Brett. thanks.

[h c]

The xlsread function of matlab R13 has a bug when reading some xls file.

Brett's one works better.

[M Calor]

Simple and good function,
thanks for disponibilization

[achim dohle]

Very good, thank you

[Dom Rolle]

Thanks a lot. Works well with 6.5 as 7!

[Ying Ding]

 

[Polsak Tothong]

Why does it ask me everytime I used this command that "A file named 'test.xls' already exists in this location. Do you want to replace it?"

[Dimitri Shvorob]

Thank you!

[Daniel BC]

I had problems using it. See below.

a = readfromexcel('table1.xls','validate','B2:N7');

Error reading range specified by input argument 2.
??? Error using ==> readfromexcel
Too many output arguments.

The function cannot see my sheet called 'validate'

[Brett Shoelson]

Daniel, You are having a problem because you are misusing the function. Read the help file...you must precede the sheetname argument with either 'sheet' or 'sheetname'.

[Pärviz Ghasemi]

 

[Rongzong Wu]

This works like a charm when a reading command is first issued. But keep asking me if I want to replace the file when it is issued the second time.

E.G:
A=ReadFromExcel('Test.xls','All'); //Fine
A=ReadFromExcel('Test.xls','All'); //???

[Rongzong Wu]

Sorry for the previous one!

It happens because I was opening the excel file with Excel while I was issuing the command.

Excelent job!

[Rob Harrison]

I've been waiting for this! Excellent - well done.

[Jun Wang]

very useful. Thanks.

[J. W.]

very helpful. thanks.

[Giacinto Busico]

This is Excel-lent! Works well. Just what I was looking for. Thank you very much.

[g w]

excellent, solved my problem!

[J.R. Diaz]

This is sooooo useful it should be included as the standard function.

[Jayavardhana Gubbi]

 

[Nilanjan Coomar]

Tremendous function. Mathworks should seriously consider incorporating it into their lib. Much, much better and more relaible than xlsread!!!!. I have laoded all sorts of xls files, csv files with it and it has not failed so far. Thanks very much

[Julia Steinberger]

Fantastic tool. Something that should maybe included in the help:
1. the arguments can be stored in a struct, if you have many ranges to read:
readfromexcel(filename, argstruct{:})
2. the results from these multiple ranges can be read into a struct of cells:
[output{1:n}] = readfromexcel(filename, argstruct{:})
3. these can be turned into double format by cell2mat:
DoubleArr(1) = cell2mat(output{1})

With this, it really works great. To write the ranges, I use two routines excelcol_num2char and excelcol_char2num , which go back and forth between excel column format and column number. I'll put them on the file exchange now :-)

[Mariam Osmane]

Hi,
I am beginner to Matlab.
where can I add this file, to make readfromexcel recognized by Matlab as defined functions?

TIA

[Alfian Abdul Halin]

Hi all :)

I am using this function and its a great2 tool.

Just have some things that I thought of:

1) When I run it, the Excel popup window will come up and ask me whether I want to save the file or not (my Excel was closed and of course the file was not open). Why does this happen ya? Can it be disabled?

2) I am using Excel 2007. I realize that when saving as 2007, this function does not work. Does this mean there is no compatibility with the office 2007 suite?

But overall... a great and easy tool to use. Excellent work by the author! :D