Path: news.mathworks.com!not-for-mail
From: <HIDDEN>
Newsgroups: comp.soft-sys.matlab
Subject: How to Import All Named Ranges From an Excel File
Date: Fri, 23 Nov 2012 23:36:07 +0000 (UTC)
Organization: HSBC
Lines: 68
Message-ID: <k8p197$4ji$1@newscl01ah.mathworks.com>
Reply-To: <HIDDEN>
NNTP-Posting-Host: www-03-blr.mathworks.com
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Trace: newscl01ah.mathworks.com 1353713767 4722 172.30.248.48 (23 Nov 2012 23:36:07 GMT)
X-Complaints-To: news@mathworks.com
NNTP-Posting-Date: Fri, 23 Nov 2012 23:36:07 +0000 (UTC)
X-Newsreader: MATLAB Central Newsreader 2921053
Xref: news.mathworks.com comp.soft-sys.matlab:783362

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;