Drexel dragonThe Math ForumDonate to the Math Forum



Search All of the Math Forum:

Views expressed in these public forums are not endorsed by Drexel University or The Math Forum.


Math Forum » Discussions » Software » comp.soft-sys.matlab

Topic: How to Import All Named Ranges From an Excel File
Replies: 1   Last Post: Nov 23, 2012 7:31 PM

Advanced Search

Back to Topic List Back to Topic List Jump to Tree View Jump to Tree View   Messages: [ Previous | Next ]
ryanshuell@gmail.com

Posts: 125
Registered: 2/11/08
How to Import All Named Ranges From an Excel File
Posted: Nov 23, 2012 6:36 PM
  Click to see the message monospaced in plain text Plain Text   Click to reply to this topic Reply

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;



Point your RSS reader here for a feed of the latest messages in this topic.

[Privacy Policy] [Terms of Use]

© Drexel University 1994-2014. All Rights Reserved.
The Math Forum is a research and educational enterprise of the Drexel University School of Education.