Date: Nov 23, 2012 6:36 PM
Author: ryanshuell@gmail.com
Subject: How to Import All Named Ranges From an Excel File
As the title states, I am trying to figure out how to import all named ranges from an Excel file. A guy named Phil was helping me with this. I still can't get it working. I had the code importing one single named range, but NOT ALL named ranges. This is what I have for code now.

function MATLABValue = GetNamedRangeFromExcel(RangeName, ExcelFile)

%GETNAMEDRANGEFROMEXCEL Retrieve a named range from an Excel workbook

% This function allow the user to retrieve a named range of cells inside

% a MATLAB variable

%

% Inputs:

% RangeName is the name of the Excel range ("named range") we want to

% retrieve in teh workbook

%

% ExcelFile is the name of the worbook

%

% Outputs:

% MATLABValue is a matrix containing the content of the named range

% from excel. If the named range is not found, an empty matrix is

% returned and a warning is issued. IT si typically a cell array . If

% one wants a numeric result, you have to call the cell2mat function

% Opening a link to excel

Excel = actxserver( 'excel.application' );

Excel.Visible = 0;

%Open the worbook specified as a function parameter

workbook = Excel.workbooks.Open( 'C:\Program Files\MATLAB\R2012a\Symbols.xls' );

% Retrieve the number of named ranges in teh workbook

NamedRangeCount = Excel.ActiveWorkBook.names.count;

% Loop on each element of the collection

if NamedRangeCount > 0

MATLABValue = cell(1,NamedRangeCount);

for ii=1:NamedRangeCount

RName=get( Excel.ActiveWorkbook.names.Item( ii ) );

% Is it the variable we are looking for ?

% It is !

RangeOfNamedCells = RName.Value;

RangeOfNamedCells(1) = [];

% Now we look in which sheet is located this variable

pos = findstr(RangeOfNamedCells,'!');

SheetName = RangeOfNamedCells(1: pos - 1);

% What is the position of this variable

NameOfTheRange = RangeOfNamedCells(pos + 1 : end);

% We go on the good sheet

xlSheet = workbook.get('Sheets',SheetName);

% Get the content of the named range

eRange = xlSheet.get('Range', NameOfTheRange);

MATLABValue{ii} = eRange.Value;

%end

end

end

if ( ~exist('MATLABValue','var') )

% Variable not found, send an empty matrix as result

warning('The named range has not been found in the specified Workbook.');

MATLABValue = [];

end;

% Close of the link to Excel

Excel.Quit;

Excel.delete;