xlrange = xlcalcrange(h,refCell,r,c,m,n)returns the full target range, xlrange in Excel A1 notation to cover an m-by-n array, starting from an offset of r rows & c columns from the starting cell, refCell.
Any workbook\worksheet prefix is removed from refcell, as are any absolute '$' markers prior to range calculation.
refcell = 'C:\directorypath\[filename.xlsm]worksheet1'!$P$6:$AC$91
refcell = P6:AC91
refcell can also be an Excel named range, but requires the Excel workbook to be open via an actxserver COM object, and a handle to the object, h, passed as a function argument. h is ignored if refcell is not a named range.
Note: this file makes use of 2 functions: dec2base27.m & base27dec.m that were sourced from inside of Mathwork's function, xlswrite.m
Please let me know of any bugs
Richard de Garis (2021). Calculate Excel Range (https://www.mathworks.com/matlabcentral/fileexchange/30180-calculate-excel-range), MATLAB Central File Exchange. Retrieved .
Worked well for me. Thanks.
You need to provide a reference cell, or range of cells, or named range e.g. A1, or A1:D3, or myStartCell
If using an explicit A1-notation range, you can prepend with the workbook and or worksheet (as in my example in the function Description). However, an Excel named range must be specified on its own.
I have just submitted an update that allows you to specify, if using an Excel named range, a particular workbook via a COM object handle, which allows for Excel named ranges in a multi-workbook environment. But the COM handle is ignored if just using an explicit A1-notation range, since that is workbook agnostic.
I have a question, can I use this function only by typing xlcalcrange('c:\mytestfile.xls') or I have to provide the selection from excel?
Can you please send me an example regarding how I can use this function for an excel file.
Works like a charm! Thanks!!
Find the treasures in MATLAB Central and discover how the community can help you!Start Hunting!