MATLAB: How to determine the range of cells in an Excel worksheet that have values in them using MATLAB 7.9 (R2009b)

excelMATLABusedrange

I am attempting to read data from a sheet in my Excel workbook using the XLSREAD function. I want to read data starting at a specific cell, for example "B9", and read all data in the sheet. Currently, the XLSREAD function does not allow me to specify this. I am only able to provide a definite range from which to read values from.

Best Answer

When you use XLSREAD to read data from an Excel file, MATLAB creates a COM connection to the Excel Application and reads data. The input argument "range" is passed to the Excel parser and so you can use any valid expression that Excel understands. Unfortunately, there is no direct mechanism to specify reading all values from a specific cell.
To determine the range of cells being used on an Excel worksheet, you can inspect the "UsedRange" variable which is provided by the Excel COM Interface. The code snippet below indicates how this can be done:
Excel = actxserver('Excel.Application');
fullPathToExcelFile = 'C:\work\myFile.xlsx'; % Please modify this to the location of your excel file
ExcelWorkbook = Excel.workbooks.Open(fullPathToExcelFile,0,true);
WorkSheets = Excel.sheets;
TargetSheet = get(WorkSheets,'item','Sheet1');
Activate(TargetSheet);
DataRange = Excel.ActiveSheet.UsedRange;
r = DataRange.Address
ExcelWorkbook.Close
Excel.Quit
Excel.delete
clear Excel WorkSheets TargetSheet DataRange ExcelWorkbook
% Using the range returned to read data
r1 = regexp(r, ':', 'split');
end_cell = regexprep(r1{2}, '\$', '');
start_cell = 'B9';
range = [start_cell ':' end_cell];
val = xlsread('myFile.xlsx', 'Sheet1', range);
Related Question