Can xlsread (Matlab) read pre-named range in Excel by range name?

Hi,

I know xlsread can read precise the range you specify, for example A= xlsread(filename,'Sheet1','I1:I2') However, I have lots of numbers to read and it is not practical to specify all the ranges like 'I1:I2'. Instead, in Excel I have defined a range name for 'I1:I2', like below, area1, however, I can't find if Matlab xlsread can read by Excel range name. Can anyone help? Thanks!

Answer 1

[Shrirang]

Hi Yiting, I think Matlab xlsread can read by Excel range name. instead of using only one return arg "A= xlsread(filename,'Sheet1','I1:I2') ", use "[A B C] = xlsread(filename,'Sheet1','I1:I2')" Now your variable B and C will containxls data. I hope this will be helpful to you.

[TingTing]

Hi Shrirang, thanks for your help. However this is not what I wanted. I just got the answer from the support team and would like to share it with you.

This is possible by simply specifying the name of the range within ''. For example, assuming my named range is called myRange, I will read the range in the following way: num = xlsread('test.xlsx','Sheet1','myRange');

However, I have tried this before myself, and it didn't work. Now I know why: in my dataset, the number has comma has 1000 separator, and I got error message from Matlab

Error using xlsread (line 247) Data range 'areano1' is invalid.

But since I see it is possible in their dataset, I removed the thousand separator and it worked!

[Shrirang]

OK!! Thank you for sharing !!

[John Wilson]

Hey Yiting,

I'm having the same issue and I'm a little confused by your solution. I think we are chasing the same problem.

Within Excel VBA: I have defined an object as a range and set it to a range of cells Dim Range1 as Range Set Range1 = Range(Cells(2,3),Cells(45,3))

In Matlab I attempt to call this range using xlsread('blahblah.xlsm','Sheet1','Range1')

I dont think I have any comma separated data. Everything in my Excel sheet is dimensioned as a double. (Possibly an issue of Range taking in Doubles instead on integers)

Thank you for figuring it out I was starting to think it was impossible..

Answer 2

[Konstantinos Sofos]

Hi Yiting,

The best and the most efficient solution in such cases is to use another function (Not Matlab standard) which is the myxlsread (attached file) * EDIT *

Example

Inputfile = 'MyFile.xls';
SheetName = 'MySheet';
MyRange1 = 'area1';

[NumericDS1,TextDS1,Excel, ExcelWorkbook] = myxlsread(Inputfile,SheetName,MyRange1 );
MyRange2 = 'A1:C100'; % this is just a dummy choise
NumericDS2= myxlsread(Inputfile,SheetName,MyRange2,Excel, ExcelWorkbook);

ExcelWorkbook.Save;
ExcelWorkbook.Close(false);
Excel.Quit

This is very fast and open/close the file once.

Regards