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 into Matlab?

Subject: How to import ALL named ranges from an Excel file into Matlab?

From: Ryan

Date: 19 Nov, 2012 02:10:17

Message: 1 of 4

I'm trying to find a way to import ALL NAMED RANGES from an Excel file into Matlab. The function below seems to work fine for ONE SINGLE NAMED RANGE. How can I import ALL NAMED RANGES?


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
    for ii=1:NamedRangeCount
        RName=get( Excel.ActiveWorkbook.names.Item( ii ) );
% Is it the variable we are looking for ?
        %if ( strcmp(RName.Name, 'EverythingElse') )
% 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 = eRange.Value;
           break;
        %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 into Matlab?

From: Phil Goddard

Date: 19 Nov, 2012 03:51:10

Message: 2 of 4

"Ryan" wrote in message <k8c4e9$1qk$1@newscl01ah.mathworks.com>...
> I'm trying to find a way to import ALL NAMED RANGES from an Excel file into Matlab. The function below seems to work fine for ONE SINGLE NAMED RANGE. How can I import ALL NAMED RANGES?
>
>
> 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

% Add the following line here
MATLABValue = cell(1,NamedRangeCount);

> for ii=1:NamedRangeCount
> RName=get( Excel.ActiveWorkbook.names.Item( ii ) );
> % Is it the variable we are looking for ?
> %if ( strcmp(RName.Name, 'EverythingElse') )
> % 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 = eRange.Value;

% Replace above line with
MATLABValue{ii} = eRange.Value;

> break;
> %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;

plus you need to modify the way the inputs to the function work and the warning message if nothing is found.

Phil.

Subject: How to import ALL named ranges from an Excel file into Matlab?

From: Ryan

Date: 20 Nov, 2012 03:54:12

Message: 3 of 4

Thanks for the help with this, Phil. However, it's still not working. Before I made the changes that you recommended, the could would import the FIRST named range. Now, with your code recommendations in place, I get only this:
  Columns 1 through 21

    {19x31 cell} [] [] [] [] [] [] [] [] [] [] [] [] [] [] [] [] [] [] [] []

  Columns 22 through 27

    [] [] [] [] [] []

Any thoughts on how to handle this???

"Phil Goddard" <phil@goddardconsulting.ca> wrote in message <k8cabe$kdh$1@newscl01ah.mathworks.com>...
> "Ryan" wrote in message <k8c4e9$1qk$1@newscl01ah.mathworks.com>...
> > I'm trying to find a way to import ALL NAMED RANGES from an Excel file into Matlab. The function below seems to work fine for ONE SINGLE NAMED RANGE. How can I import ALL NAMED RANGES?
> >
> >
> > 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
>
> % Add the following line here
> MATLABValue = cell(1,NamedRangeCount);
>
> > for ii=1:NamedRangeCount
> > RName=get( Excel.ActiveWorkbook.names.Item( ii ) );
> > % Is it the variable we are looking for ?
> > %if ( strcmp(RName.Name, 'EverythingElse') )
> > % 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 = eRange.Value;
>
> % Replace above line with
> MATLABValue{ii} = eRange.Value;
>
> > break;
> > %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;
>
> plus you need to modify the way the inputs to the function work and the warning message if nothing is found.
>
> Phil.

Subject: How to import ALL named ranges from an Excel file into Matlab?

From: Phil Goddard

Date: 20 Nov, 2012 05:53:08

Message: 4 of 4

So you're still only getting the first one.
(Note that the output is now a cell array.
Each named range is being place in a separate element of the cell array.
If you aren't sure how to use cell arrays then look at the examples in the documentation.)

To get the rest of them you also need to comment out the line that says "break", which is stopping the code after the first name range is read.

Phil.

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