Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
How to Import All Named Ranges From an Excel File

Subject: How to Import All Named Ranges From an Excel File

From: Ryan

Date: 23 Nov, 2012 23:36:07

Message: 1 of 2

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;

Subject: How to Import All Named Ranges From an Excel File

From: Ryan

Date: 24 Nov, 2012 00:31:07

Message: 2 of 2

Whoops, I forgot to post the error message!!
GetNamedRangeFromExcel
37 RangeOfNamedCells = RName.Value;
Error using Interface.000208DA_0000_0000_C000_000000000046/get
Invoke Error, Dispatch Exception: Invalid index.



Error in GetNamedRangeFromExcel (line 47)
           xlSheet = workbook.get('Sheets',SheetName);

"Ryan" wrote in message <k8p197$4ji$1@newscl01ah.mathworks.com>...
> 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;

Tags for this Thread

No tags are associated with this thread.

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us