Date: Nov 23, 2012 6:36 PM
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;


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 = [];

% Close of the link to Excel