Read Excel Range which is a "Table" is not working with readtable(​file,'Rang​e','NameOf​Range')

How can i read a Range in excel which is a table, like in screenshot "Doof" or "excelTab_Zahl" ?

The access to "Dumm" is working but not all the others, despide of they all are listed in the name Manager (see screenshot)

My code:

opts = detectImportOptions('Excel_Read_Write_Matlab.xlsx')

%Working:

data = readtable('Excel_Read_Write_Matlab.xlsx', 'Range', 'Dumm'); %'excelTab_Text'

%Not Working:

data = readtable('Excel_Read_Write_Matlab.xlsx', 'Range', 'Doof'); %'excelTab_Text'

data = readtable('Excel_Read_Write_Matlab.xlsx', 'Range', 'excelTab_Text'); %'excelTab_Text'

data = readtable('Excel_Read_Write_Matlab.xlsx', 'Range', 'excelTab_Zahl'); %'excelTab_Text'

Result of "My code":

opts =

SpreadsheetImportOptions with properties:

Sheet Properties:

Sheet: ''

Replacement Properties:

MissingRule: 'fill'

ImportErrorRule: 'fill'

Variable Import Properties: Set types by name using setvartype

VariableNames: {'A', 'B', 'C' ... and 6 more}

VariableTypes: {'double', 'char', 'char' ... and 6 more}

SelectedVariableNames: {'A', 'B', 'C' ... and 6 more}

VariableOptions: Show all 9 VariableOptions

Access VariableOptions sub-properties using setvaropts/getvaropts

VariableNamingRule: 'modify'

Range Properties:

DataRange: 'A2' (Start Cell)

VariableNamesRange: 'A1'

RowNamesRange: ''

VariableUnitsRange: ''

VariableDescriptionsRange: ''

To display a preview of the table, use preview

Error using readtable

Unable to determine range. Range must be of the form 'A1' (cell), 'A:B' (column-select), '1:5' (row-select), 'A1:B5'

(rectangle-select), or a valid named range in the sheet.

Screenshot of Excel and the Range name Manager:

[Stephen23]

Unfortunately tables are somewhat different to named ranges (although in general it is possible to convert between them). Perhaps this is just an oversight by TMW: you should make an enhancement request:

https://www.mathworks.com/matlabcentral/answers/460998-how-can-i-make-a-features-request

[Sebastian Neubauer]

I tried to do so: Case Number 06049647

Answer 1

[Sebastian Neubauer]

"[...] This requires to set import optional Name-Value pair 'UseExcel', 'on' when calling readtable. An enhancement request has already been created in our internal documetnation and is under consideration for a future release. Until then, setting 'UseExcel' is mandatory when using named ranges.[...]"

Answer 2

[Timo Dietz]

Maybe I'm wrong, but I assume Matlab can not access Excel range definitions ('Namens-Manager') and you have to address column and row by their identifiers ('A1', 'A:B', ...).

See the opts output. Your range definitions are not listed. Just my assumption.

[Sebastian Neubauer]

My range "Dumm" is working the other ranges are not working even if they look same / simular in Name Manger

[Stephen23]

"Maybe I'm wrong, but I assume Matlab can not access Excel range definitions ('Namens-Manager') and you have to address column and row by their identifiers ('A1', 'A:B', ...)."

The READTABLE documentation gives this option for the RANGE option:

Source: https://www.mathworks.com/help/matlab/ref/readtable.html#namevaluepairarguments

[Timo Dietz]

You are certainly right - I was not aware of this. Good for me, I learned something but unfortunately, this does not solve Sebastian's issue. :-(

[Sebastian Neubauer]

Is there maybe a workarround possible?

I can not say how important this topic is for us. It is hard to explain the tables feature in excel since all think the excel sheet is a table but it is not. The actual implementation does not allow to use excel in the best way by just entering data -> Mark the data --> Press Strg+T -> give a Name to the Table. The so created table does automatically change location and size. A normal range does not.

[Timo Dietz]

Have you tried 'readcell' instead of readtable? I encountered that readtable seems to interpret the range differently depending on the cell content. If it's numeric, all data is in rows and the table col names (VariableNames) are just 'Var x', if the cells contain text, the first row in the range is taken as variable names for the table and thus is not part of the data. Not what I assumed. I think it's worth a try.

[Sebastian Neubauer]

I got the answer to use the 'UseExcel' option.

"[...] This requires to set import optional Name-Value pair 'UseExcel', 'on' when calling readtable. An enhancement request has already been created in our internal documetnation and is under consideration for a future release. Until then, setting 'UseExcel' is mandatory when using named ranges.[...]"