Search All of the Math Forum:
Views expressed in these public forums are not endorsed by
NCTM or The Math Forum.



How to Import All Named Ranges From an Excel File
Posted:
Nov 23, 2012 6:36 PM


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;



