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: 114
Registered: 2/11/08
Re: How to Import All Named Ranges From an Excel File
Posted: Nov 23, 2012 7:31 PM
  Click to see the message monospaced in plain text Plain Text   Click to reply to this topic Reply

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;




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.